SET NOCOUNT ON
if object_id('tempdb.dbo.#tem1') is null
create table #tem1
(name varchar(100),
tbefore int default 0
)
else
create table #tem2
(name varchar(100),
tafter int default 0
)
declare @sql varchar(1000)
declare cur cursor for
select 'select '''+name+''', count(*) from '+name as sql from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by refdate desc
open cur
fetch next from cur into @sql
while @@fetch_status=0
begin
if object_id('tempdb.dbo.#tem2') is null
insert into #tem1(name,tbefore)
exec(@sql)
else
insert into #tem2(name,tafter)
exec(@sql)
fetch next from cur into @sql
end
close cur
deallocate cur
if object_id('tempdb.dbo.#tem2') is not null
begin
select a.name 表名,a.tbefore as 操作前的行数,b.tafter as 操作后的行数 from #tem1 a,#tem2 b where a.name=b.name and a.tbefore<>b.tafter
drop table #tem1
drop table #tem2
end else
print '请插入记录后,再执行一次这段语句'
SET NOCOUNT OFF
这段代码可以适合这个需求:
用户在客户端插入一条记录后,通过这段代码可以查出这动作影响了哪几个表
适合于做数据分析,或分析数据库时使用
使用方法:在SQL查询分析器中先运行一下这代码,再在客户端插入一条记录,然后再运行一下这代码就列出来了。