MS SQL Server 表数据更新时间查询

检查数据库表最近一次被INSERT/UPDATE/DELETE的时间来了解各个表的数据写入情况。这里通过一个脚本给每个表加上触发器来记录表数据最后修改时间等信息。

执行以下T-SQL语句，将在当前数据库中创建一个日志表 [dbo].[generic_tbmodify_log] ，并自动从 sysobjects 系统表中获得数据库表清单，对除 [dbo].[generic_tbmodify_log] 本身以外的所有用户表创建触发器（触发器名形如 TG_FOR_GENETBMODIFY_[表名] ）。这样当这些表发生 insert / update / delete 的时候，会自动更新 generic_tbmodify_log 表相应表名的表数据最后更新时间。

 

1. -- create table to hold updated message and date   
2. -- tested in MS SQL Server 2000   
3. if exists (   
4.   select * from dbo.sysobjects   
5.     where id = object_id(N'[dbo].[generic_tbmodify_log]')   
6.       and OBJECTPROPERTY(id, N'IsUserTable') = 1   
7. ) drop table [dbo].[generic_tbmodify_log]   
8. GO   
9. CREATE TABLE [dbo].[generic_tbmodify_log]   
10.        ([PKID] [int] IDENTITY (1, 1) NOT NULL ,   
11.        [tb_name] [varchar] (128)   
12.          COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,   
13.        [info] [varchar] (128)   
14.          COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,   
15.        [last_modified] [datetime] NULL ) ON [PRIMARY]   
16. GO   
17. ALTER TABLE [dbo].[generic_tbmodify_log] WITH NOCHECK   
18.   ADD CONSTRAINT [PK_generic_tbmodify_log] PRIMARY KEY  
19.     CLUSTERED ([PKID]) ON [PRIMARY]   
20. GO   
21. -- for each table in database generate audit trigger   
22. -- except generic_tbmodify_log, dtproperties   
23. DECLARE @TABLENAME VARCHAR(50)   
24. DECLARE @SQLCMD VARCHAR(2000)   
25. DECLARE TABLES_CURSOR CURSOR FOR  
26.      SELECT Name from dbo.sysobjects   
27.         where xtype = 'U'  
28.         AND NAME <> 'dtproperties'  
29.         AND NAME <> 'generic_tbmodify_log'  
30. OPEN TABLES_CURSOR   
31.   
32. FETCH NEXT FROM TABLES_CURSOR INTO @TABLENAME   
33. WHILE (@@FETCH_STATUS = 0)   
34. BEGIN  
35.  -- BUILD CREATE TRIGGER STATEMENT   
36.  SET @SQLCMD = (SELECT 'CREATE TRIGGER [TG_FOR_GENETBMODIFY_')   
37.  SET @SQLCMD = @SQLCMD +   
38.      @TABLENAME + '] ON [DBO].[' + @TABLENAME + ']' +   
39.      ' FOR INSERT, UPDATE, DELETE AS' +   
40.      ' DECLARE @CNTINSERT INTEGER' +   
41.      ' DECLARE @CNTDELETE INTEGER' +   
42.      ' DECLARE @INFOSTR   VARCHAR(128)' +   
43.      ' SELECT @CNTINSERT = (SELECT COUNT(*) FROM Inserted)' +   
44.      ' SELECT @CNTDELETE = (SELECT COUNT(*) FROM Deleted)' +   
45.      ' SET @INFOSTR=''''' +   
46.      ' IF @CNTINSERT > 0 BEGIN' +   
47.      '   IF @CNTDELETE > 0' +   
48.      '     SET @INFOSTR = @INFOSTR + ''UPD:''' +   
49.      '   ELSE' +   
50.      '     SET @INFOSTR = @INFOSTR + ''INS:''' +   
51.      '   SET @INFOSTR = @INFOSTR + '+   
52.      '         master.dbo.fn_varbintohexsubstring('+   
53.      '           1,COLUMNS_UPDATED(),1,0)' +   
54.      ' END ELSE SET @INFOSTR = @INFOSTR + ''DEL''' +   
55.      ' IF NOT EXISTS' +   
56.      ' (SELECT DBA.TB_NAME FROM' +   
57.      '   DBO.GENERIC_TBMODIFY_LOG DBA' +   
58.      '   WHERE TB_NAME = ''' + @TABLENAME + ''')' +   
59.      ' BEGIN' +   
60.      '   INSERT INTO GENERIC_TBMODIFY_LOG' +   
61.      '    (tb_name,info,last_modified) VALUES ' +   
62.      '    (''' + @TABLENAME + ''',' +   
63.      '     @INFOSTR,' +   
64.      '     Getdate()' +   
65.      '    )' +   
66.      ' END' +   
67.      ' ELSE' +   
68.      ' BEGIN' +   
69.      '   UPDATE GENERIC_TBMODIFY_LOG SET' +   
70.      '     info=@INFOSTR,' +   
71.      '     last_modified=GETDATE()' +   
72.      '   WHERE tb_name=''' + @TABLENAME + '''' +   
73.      ' END'  
74.  -- EXECUTE CREATE TRIGGER STATEMENT   
75.  EXEC (@SQLCMD) FETCH NEXT FROM TABLES_CURSOR INTO @TABLENAME   
76. END  
77. CLOSE TABLES_CURSOR   
78. DEALLOCATE TABLES_CURSOR   

相应的如果要移除以上脚本创建的所有trigger，可以执行以下语句：

 

1. declare  @name     varchar(500)   
2. declare  mycursor  cursor  
3. for  
4. select name from sysobjects   
5.   where type = 'tr' and  
6.   name like 'TG_FOR_GENETBMODIFY_%'  
7.   
8. open mycursor   
9. fetch next from mycursor into @name  
10.   
11. while @@fetch_status = 0   
12. begin  
13.     exec ('drop trigger ' + @name)   
14.     select 'Deleted ' + @name  
15.     fetch next from mycursor into @name  
16. end  
17. close mycursor   
18. deallocate mycursor