检查数据库表最近一次被INSERT/UPDATE/DELETE的时间来了解各个表的数据写入情况。这里通过一个脚本给每个表加上触发器来记录表数据最后修改时间等信息。
执行以下T-SQL语句,将在当前数据库中创建一个日志表 [dbo].[generic_tbmodify_log] ,并自动从 sysobjects 系统表中获得数据库表清单,对除 [dbo].[generic_tbmodify_log] 本身以外的所有用户表创建触发器(触发器名形如 TG_FOR_GENETBMODIFY_[表名] )。这样当这些表发生 insert / update / delete 的时候,会自动更新 generic_tbmodify_log 表相应表名的表数据最后更新时间。
- -- 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