监控数据库表变化

CREATE PROCEDURE  usr_run_1 AS
declare @name  varchar(50)
declare @id  int
declare @count int
declare @sql nvarchar(255)
select id,name  into #tmp20140215  from sysobjects where type='u' order by name
select @count=count(*) from #tmp20140215
print cast (@count as varchar(10))
while exists(select * from #tmp20140215)
begin
         select @id=id,@name=name from #tmp20140215
      --   usr_run_2 @name, @count output
         set @sql='use ais20140121142916 select @count=count(*) from ' +@name
          exec master.dbo.sp_executesql @sql,N'@count int output',@count output
          insert into a99 (tab_name,first_run) values (@name,@count)
         
         delete from #tmp20140215 where id=@id
         
end


select @count=count(*) from a99

print cast (@count as varchar(10))

CREATE PROCEDURE usr_run_2 AS


declare @name  varchar(50)
declare @id  int
declare @count int
declare @sql nvarchar(255)
select id,name  into #tmp201402151  from sysobjects where type='u' order by name
while exists(select * from #tmp201402151)
begin
         select @id=id,@name=name from #tmp201402151
      --   usr_run_2 @name, @count output
         set @sql='use ais20140121142916 select @count=count(*) from ' +@name
          exec master.dbo.sp_executesql @sql,N'@count int output',@count output
         -- insert into a99 (tab_name,first_run) values (@name,@count)
          update a99 set second_run=@count where tab_name=@name
         
         delete from #tmp201402151 where id=@id
         
end

数据名叫 ais201401211429,创建了一个表a99   字段 tab_name,first_run,second_run

先运行 usr_run_1,然后添加一条记录,再运行usr_run_2

就\在a99中就可以看到先后的不同