查询指定的表在那些数据库中存在

--查询指定的表在那些数据库中存在

declare @tbname sysname

set @tbname='客户资料'

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)