查询指定的表在那些数据库中存在(转)[@more@]

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8225414/viewspace-944379/，如需转载，请注明出处，否则将追究法律责任。