查询SQL Server数据字典以查找包含表“x”的所有数据库。

Question

我对SQL Server数据字典不是特别熟悉，但我认为我尝试做的事情是可能的。

我们有许多复制的数据库，名称不同，比如： Client1 Client2 Client3

我希望不依赖命名约定，而是根据它们是否包含一个关键表（称之为MyTable）来识别这些数据库。因此，我开始思考需要以下查询：

SELECT db.name
FROM sys.databases db
JOIN sys.tables tbl ON ??
WHERE tbl.Name = 'MyTable'

这个方法行不通，因为我无法直接或间接地将sys.tables与sys.databases连接起来，而且sys.tables是基于活动数据库的视图，而不是所有数据库的完整表集。

有人能否为这种情况找到一个合适的查询语句？

- MattH

Answer 1

尝试使用未记录的 sp_MSforeachdb

EXECUTE master.sys.sp_MSforeachdb 
'select table_catalog from 
information_schema.tables where table_name like ''MyTable%'''

唯一想到的以查询方式执行的方法，是动态构建选择语句 （在此插入标准的动态SQL警告/不赞成意见）。

 Declare @SQL varchar(max)
    Set @SQL = ''
    Select @SQL = @SQL + Coalesce('Select Distinct 
    table_catalog from ' + name  + '.information_schema.tables 
    where table_name like ''mytable%''  UNION ','' )
    from sys.databases where state_desc = 'ONLINE'
    and collation_name =  'SQL_Latin1_General_CP1_CI_AS'
    set @SQL =  Substring(@SQL, 1, Len(@SQL) - 6) + ' order by table_catalog  '
    exec (@SQL)

*注意：我添加了一些可用数据库的状态和排序标准。

- cmsjr

---

为了我的目的，我将适应以下内容，因为我需要一个单一的查询结果。如果有人拥有一个纯查询答案，仍然希望如此。 创建表＃ClientDbs（     Name varchar（max） ）执行master.sys.sp_MSforeachdb '   USE？     INSERT INTO #ClientDbs     SELECT table_catalog     FROM INFORMATION_SCHEMA.TABLES     WHERE table_name LIKE''Profile'' ' SELECT * FROM #ClientDbs DROP TABLE #ClientDbs - MattH