监控数据库表变化

来源：互联网发布：云朵课堂网络教学软件编辑：程序博客网时间：2024/06/05 15:30

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中就可以看到先后的不同

0 0