查询指定的表在那些数据库中存在
敬业的IT人
互联网
佚名
2008-1-8 16:17:55
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)
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)
- 最新文章
- 数据库学习:查询新身份证(18位)的校验位[01-08]
- 数据库事务和锁(一)[01-08]
- 数据库事务和锁(二)[01-08]
- 数据库学习:用Access分析网站实例[01-08]
- 数据库学习:查询区分大小写[01-08]
- 将表中字段转换,并保留原值[01-08]
- 相关文章
- 数据库学习:查询新身份证(18位)的校验位[01-08]
- 数据库事务和锁(一)[01-08]
- 数据库事务和锁(二)[01-08]
- 数据库学习:用Access分析网站实例[01-08]
- 数据库学习:查询区分大小写[01-08]
- 数据库学习:在视图中用order by[01-08]
