查询表属于哪个数据

 

s
declare @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