由于项目需要,需要从sql server数据库导入数据,但是没有相关的数据字典,只知道数据库名,但是表的个数却多达500多,于是我们怎样才能知道我们需要的指定表呢?
一、创建一个日志记录表,并为其他所有的表创建触发器,记录每个表的变化
-- create table to hold updated message and date
-- tested in MS SQL Server 2000
if exists (
select * from dbo.sysobjects
where id = object_id(N'[dbo].[generic_tbmodify_log]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1
) drop table [dbo].[generic_tbmodify_log]
GO
CREATE TABLE [dbo].[generic_tbmodify_log]
([PKID] [int] IDENTITY (1, 1) NOT NULL ,
[tb_name] [varchar] (128)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[info] [varchar] (128)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[last_modified] [datetime] NULL ) ON [PRIMARY]
GO
ALTER TABLE [dbo].[generic_tbmodify_log] WITH NOCHECK
ADD CONSTRAINT [PK_generic_tbmodify_log] PRIMARY KEY
CLUSTERED ([PKID]) ON [PRIMARY]
GO
-- for each table in database generate audit trigger
-- except generic_tbmodify_log, dtproperties
DECLARE @TABLENAME VARCHAR(50)
DECLARE @SQLCMD VARCHAR(2000)
DECLARE TABLES_CURSOR CURSOR FOR
SELECT Name from dbo.sysobjects
where xtype = 'U'
AND NAME <> 'dtproperties'
AND NAME <> 'generic_tbmodify_log'
OPEN TABLES_CURSOR
FETCH NEXT FROM TABLES_CURSOR INTO @TABLENAME
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- BUILD CREATE TRIGGER STATEMENT
SET @SQLCMD = (SELECT 'CREATE TRIGGER [TG_FOR_GENETBMODIFY_')
SET @SQLCMD = @SQLCMD +
@TABLENAME + '] ON [DBO].[' + @TABLENAME + ']' +
' FOR INSERT, UPDATE, DELETE AS' +
' DECLARE @CNTINSERT INTEGER' +
' DECLARE @CNTDELETE INTEGER' +
' DECLARE @INFOSTR VARCHAR(128)' +
' SELECT @CNTINSERT = (SELECT COUNT(*) FROM Inserted)' +
' SELECT @CNTDELETE = (SELECT COUNT(*) FROM Deleted)' +
' SET @INFOSTR=''''' +
' IF @CNTINSERT > 0 BEGIN' +
' IF @CNTDELETE > 0' +
' SET @INFOSTR = @INFOSTR + ''UPD:''' +
' ELSE' +
' SET @INFOSTR = @INFOSTR + ''INS:''' +
' SET @INFOSTR = @INFOSTR + '+
' master.dbo.fn_varbintohexsubstring('+
' 1,COLUMNS_UPDATED(),1,0)' +
' END ELSE SET @INFOSTR = @INFOSTR + ''DEL''' +
' IF NOT EXISTS' +
' (SELECT DBA.TB_NAME FROM' +
' DBO.GENERIC_TBMODIFY_LOG DBA' +
' WHERE TB_NAME = ''' + @TABLENAME + ''')' +
' BEGIN' +
' INSERT INTO GENERIC_TBMODIFY_LOG' +
' (tb_name,info,last_modified) VALUES ' +
' (''' + @TABLENAME + ''',' +
' @INFOSTR,' +
' Getdate()' +
' )' +
' END' +
' ELSE' +
' BEGIN' +
' UPDATE GENERIC_TBMODIFY_LOG SET' +
' info=@INFOSTR,' +
' last_modified=GETDATE()' +
' WHERE tb_name=''' + @TABLENAME + '''' +
' END'
-- EXECUTE CREATE TRIGGER STATEMENT
EXEC (@SQLCMD) FETCH NEXT FROM TABLES_CURSOR INTO @TABLENAME
END
CLOSE TABLES_CURSOR
DEALLOCATE TABLES_CURSOR
二、尝试在前台做一个相关的操作,然后查询我们刚才建立的日志表就知道改变了哪些表了
三、相应的如果要移除以上脚本创建的所有trigger,请参照下面的代码
declare @name varchar(500)
declare mycursor cursor
for
select name from sysobjects
where type = 'tr' and
name like 'TG_FOR_GENETBMODIFY_%'
open mycursor
fetch next from mycursor into @name
while @@fetch_status = 0
begin
exec ('drop trigger ' + @name)
select 'Deleted ' + @name
fetch next from mycursor into @name
end
close mycursor
deallocate mycursor