[技术共享]如何在服务器中查询表所在的数据库名称

很久没上****了，****依旧还是那么的火，在此祝愿DN越办越好。见贴者均有分~
今天来给大家出个题，顺便也把好久没写的SQL从新写一把
题目:如何在服务器中查询表所在的数据库名称.
已知表名，在服务器中的任意数据库中执行脚本可得出表所在的数据库名称。

举例
现在有数据库DB_A，表包含A1,A2,A3,A4，A10
数据库DB_B，表包含A5，A6，A7，A8,A10
在数据库DB_B中执行脚本 输入表名为A1 能够得知此表在数据库DB_A中 反之亦然。数据库中存在相同表名称，需要检索出所有数据库名称。

楼主我自己先写一个奉上，希望有人能提供更好的解决方案~ 谢谢！
[code=SQ]
USE [master]
DECLARE @TableName VARCHAR(100);
DECLARE @QueryString VARCHAR(MAX);

SET @TableName = 'tables'

SELECT @QueryString =REPLACE(REPLACE( 
(
SELECT 'select name collate chinese_prc_ci_as as TableNAME,''' 
+ dbName_Query.DATABASE_NAME 
+ ''' AS DATABASE_NAME from ' 
+ dbName_Query.DATABASE_NAME + 
       '.dbo.sysobjects WHERE xtype = ''U'' union all ' AS DBQueryString
FROM   (
           SELECT DATABASE_NAME = DB_NAME(s_mf.database_id)
           FROM   sys.master_files s_mf
           WHERE  s_mf.state = 0
           AND    HAS_DBACCESS(DB_NAME(s_mf.database_id)) = 1 
           AND DB_NAME(s_mf.database_id) 
           NOT IN('master','tempdb','model','msdb')
           GROUP BY
                  s_mf.database_id
       ) AS dbName_Query FOR XML AUTO
), '<dbName_Query DBQueryString="', ''),'"/>','')
SET @QueryString = 'select * from ( ' + SUBSTRING(@QueryString,1,LEN(@QueryString)-10) + ') As Query where Query.TableNAME like ''%' + @TableName + '%'' ';
PRINT @QueryString
EXEC(@QueryString)
[/code]

20 个解决方案

#1

就拼一个动态字符串而已，又何必 for xml 那么复杂

#2

#3

[code=SQ]
use master
-- 得到所有数据库名
select name from [sysdatabases]
-- 得到某数据库所有表名
select name from [sysobjects] where [type] = 'u'

--貌似需要做个循环
[/code]

#4

该回复于2011-02-19 08:59:48被版主删除

#5

该回复于2011-02-19 08:59:47被版主删除

#6

该回复于2011-02-19 09:02:26被版主删除

#7

引用 1 楼 linares 的回复:

就拼一个动态字符串而已，又何必 for xml 那么复杂

其实啊 这里的动态字符串 之所以用到for xml 是因为在这里 你需要进行字符串聚合， 在没有做CLR自定义聚合函数的情况下 使用这种方式 可以高效快速的进行字符串聚合 否则需要写一个表值函数进行转换（效率低，而且消耗较大）

#8

引用 3 楼 maco_wang 的回复:

[code=S]
use master
-- 得到所有数据库名
select name from [sysdatabases]
-- 得到某数据库所有表名
select name from [sysobjects] where [type] = 'u'

--貌似需要做个循环
[/code]

恩 不错 之前我写的一版是用的游标，因为效率太低了 所以我想到了字符串聚合的方式 动态拼接SQL 来实现。 

#9

还有一点 需要补充的就是 每一个数据库的默认排序方案（可能会被修改） 是不一样的 这里之所以用到 collate chinese_prc_ci_as 就是因为这个原因 否则 这些从不同数据库中获取的数据 是无法进行UNION ALL的。

#10

引用 7 楼 shanguotao20 的回复:

引用 1 楼 linares 的回复:

就拼一个动态字符串而已，又何必 for xml 那么复杂

其实啊 这里的动态字符串 之所以用到for xml 是因为在这里 你需要进行字符串聚合， 在没有做CLR自定义聚合函数的情况下 使用这种方式 可以高效快速的进行字符串聚合 否则需要写一个表值函数进行转换（效率低，而且消耗较大）

不觉得 XML 字符聚合有多高效，而且这个题目不需要聚合。

DECLARE @TableName VARCHAR(100);
SET @TableName = 'tables'

declare @sql varchar(max)

select @sql = isnull(@sql+' union all ', '') + 'select '''+db_name(database_id)+''' database_name, name collate Chinese_PRC_CI_AS table_name from ' +db_name(database_id) + '..sysobjects where xtype = ''U'' and name collate Chinese_PRC_CI_AS like ''%'+@TableName+'%'''
from sys.master_files
where state = 0
and has_dbaccess(db_name(database_id)) = 1
and db_name(database_id)
not in('master','tempdb','model','msdb')
group by database_id

exec (@sql)

#11

引用 10 楼 linares 的回复:

引用 7 楼 shanguotao20 的回复:

引用 1 楼 linares 的回复:

就拼一个动态字符串而已，又何必 for xml 那么复杂

其实啊 这里的动态字符串 之所以用到for xml 是因为在这里 你需要进行字符串聚合， 在没有做CLR自定义聚合函数的情况下 使用这种方式 可以高效快速的进行字符串聚合 否则需要写一个表值函数进行转换（效率低，而且消耗较大）

……

优化的 太好了~ 赞一个~ 学习了~！

#12

引用 10 楼 linares 的回复:

DECLARE @TableName VARCHAR(100);
SET @TableName = 'tables'

declare @sql varchar(max)

select @sql = isnull(@sql+' union all ', '') + 'select '''+db_name(database_id)+''' database_name, name collate Chinese_PRC_CI_AS table_name from ' +db_name(database_id) + '..sysobjects where xtype = ''U'' and name collate Chinese_PRC_CI_AS like ''%'+@TableName+'%'''
from sys.master_files
where state = 0
and has_dbaccess(db_name(database_id)) = 1
and db_name(database_id)
not in('master','tempdb','model','msdb')
group by database_id

exec (@sql)

+

#13

凑热闹:

declare @sql nvarchar(max),@tbname nvarchar(100)
set @tbname='tb'
set @sql=''
select @sql=@sql+'select '''+name+''' where exists(select 1 from '+name+'.dbo.sysobjects where name like ''%'+@tbname+'%'') union all ' 
from sys.databases where log_reuse_wait>0
set @sql=left(@sql,len(@sql)-10)
exec(@sql)

#14

如果包含系统数据库,把 where 去掉.

#15

加个"表"限制:

declare @sql nvarchar(max),@tbname nvarchar(100)
set @tbname='tb'
set @sql=''
select @sql=@sql+'select '''+name+''' where exists(select 1 from '+name+'.dbo.sysobjects where name like ''%'+@tbname+'%'' and type=''U'') union all ' 
from sys.databases where log_reuse_wait>0
set @sql=left(@sql,len(@sql)-10)
exec(@sql)

#16

#17

引用 13 楼 qianjin036a 的回复:

凑热闹:
SQL code
declare @sql nvarchar(max),@tbname nvarchar(100)
set @tbname='tb'
set @sql=''
select @sql=@sql+'select '''+name+''' where exists(select 1 from '+name+'.dbo.sysobjects where name like ……

这位仁兄的方法 未达到要求哈~ 这个方法 无法在服务器中跨数据库执行。。 

#18

[code=SQ]use master
go
create database db1
go
use db1
go
create table tb1(id int)
go
create database db2
go
use db2
go
create table mytable(id int)
go
create database db3
go
use db3
create table mytb(id int)
go
use master
declare @sql nvarchar(max),@tbname nvarchar(100)
set @tbname='tb'
set @sql=''
select @sql=@sql+'select '''+name+''' where exists(select 1 from '+name+'.dbo.sysobjects where name like ''%'+@tbname+'%'' and type=''U'') union all ' 
from sys.databases
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
/*
-------------------
db1
db3

(2 行受影响)

*/
go
drop database db1,db2,db3[/code]

#19

use master
go
create database db1
go
use db1
go
create table tb1(id int)
go
create database db2
go
use db2
go
create table mytable(id int)
go
create database db3
go
use db3
create table mytb(id int)
go
use master
declare @sql nvarchar(max),@tbname nvarchar(100)
set @tbname='tb'
set @sql=''
select @sql=@sql+'select '''+name+''' where exists(select 1 from '+name+'.dbo.sysobjects where name like ''%'+@tbname+'%'' and type=''U'') union all ' 
from sys.databases --where log_reuse_wait>0
set @sql=left(@sql,len(@sql)-10)
exec(@sql)
/*
-------------------
db1
db3

(2 行受影响)

*/
go
drop database db1,db2,db3

#20

引用 19 楼 qianjin036a 的回复:

SQL code
use master
go
create database db1
go
use db1
go
create table tb1(id int)
go
create database db2
go
use db2
go
create table mytable(id int)
go
create database db3
go
use db3
create table myt……

That's so nice! 这回可以了~！

#21

• 如何查询数据库中表的字段名？