sdeclare @tab varchar(50)set @tab='TxWebAnalysisResult'declare @t
table (Id int identity,dbname varchar(50),tbname varchar(50))declare @db varchar(50)
declare roy cursor for select name from
master.dbo.sysdatabasesOpen royfetch next from
roy into @dbwhile @@fetch_status=0begindeclare
@sql nvarchar(4000),@i intset
@sql='if exists(select 1 from '+@db+'.dbo.sysobjects where xtype=''u'' and name='''+@tab+''')set @i=1'exec
sp_executesql @sql,N'@i int output',@i outputif @i=1insert into @t select @db,@tabfetch next from roy into
@dbendclose roydeallocate royselect * from @tdeclare @tab varchar(50)set @tab='TxWebAnalysisResult'--tableName 表名
declare @t table (Id int identity,dbname varchar(50),tbname varchar(50))declare @db varchar(50)
declare roy cursor for select name from master.dbo.sysdatabases
Open roy
fetch next from roy into @dbwhile @@fetch_status=0begindeclare
@sql nvarchar(4000),@i intset
@sql='if exists(select 1 from '+@db+'.dbo.sysobjects where xtype=''u'' and name='''+@tab+''')set @i=1'exec
sp_executesql @sql,N'@i int output',@i output
if @i=1insert into @t select @db,@tab
fetch next from roy into @db
endclose roydeallocate royselect * from @t
代码
