declare @tbname sysname set @tbname='tb'--表名 declare @dbname sysname,@sql nvarchar(4000),@re bit,@sql1 varchar(8000) set @sql1='' declare tb cursor for select name from master..sysdatabases open tb fetch next from tb into @dbname while @@fetch_status=0 begin set @sql='set @re=case when exists(select 1 from [' +@dbname+']..sysobjects where xtype=''U'' and name=''' +@tbname+''') then 1 else 0 end' exec sp_executesql @sql,N'@re bit out',@re out if @re=1 set @sql1=@sql1+' union all select '''+@dbname+'''' fetch next from tb into @dbname end close tb deallocate tb set @sql1=substring(@sql1,12,8000) exec(@sql1)
EXEC sp_MsForeachdb N'USE [?] IF OBJECT_ID(''tb'') IS NOT NULL PRINT ''?'''
set @tbname='tb'--表名
declare @dbname sysname,@sql nvarchar(4000),@re bit,@sql1 varchar(8000)
set @sql1=''
declare tb cursor for select name from master..sysdatabases
open tb
fetch next from tb into @dbname
while @@fetch_status=0
begin
set @sql='set @re=case when exists(select 1 from ['
+@dbname+']..sysobjects where xtype=''U'' and name='''
+@tbname+''') then 1 else 0 end'
exec sp_executesql @sql,N'@re bit out',@re out
if @re=1 set @sql1=@sql1+' union all select '''+@dbname+''''
fetch next from tb into @dbname
end
close tb
deallocate tb
set @sql1=substring(@sql1,12,8000)
exec(@sql1)
IF OBJECT_ID(''tb'') IS NOT NULL
PRINT ''?'''