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

发布于 2024-07-29 21:07:30 字数 429 浏览 8 评论 0原文

我对 SQL Server 数据字典不太熟悉，但我假设我尝试做的事情是可能的。

我们有许多复制的数据库，它们的名称不同：客户1 客户端2 Client3

我希望根据这些数据库是否包含关键表（将其称为 MyTable）来识别这些数据库，而不是依赖命名约定。所以开始考虑需要像下面这样的查询：

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

这不起作用，因为我看不到如何直接或间接地将 sys.tables 连接到 sys.databases，而且 sys.tables 是基于活动的视图数据库，而不是所有数据库的完整表集。

任何人都可以为这种情况确定合适的查询吗？

原文

I'm not overly familiar with the SQL Server data dictionary, but I've assumed what I'm trying to do is possible.

We have a number of replicated databases, under different names say:
Client1
Client2
Client3

Rather than rely on a naming convention, I was hoping to identify these databases, based on whether they include a key table, call it MyTable. So started thinking a query such as the following was needed:

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

This doesn't work, as I can't see how to join sys.tables to sys.databases directly or indirectly, and also sys.tables is a view based on the active database, rather than a complete set of tables for all databases.

Can anyone identify a suitable query for this situation?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

天荒地未老 2024-08-05 21:07:30

尝试未记录的 sp_MSforeachdb

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

作为查询执行此操作的唯一方法是动态构建 select 语句（在此处插入标准动态 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)

*注意我添加了一些状态条件以及可用数据库的整理。

Try the undocumented sp_MSforeachdb

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

The only way that comes to mind to do it as a query is to build the select statement dynamically (insert standard dynamic sql warning/disapproval here)

 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)

*NOTE I added some criteria for the state and collation of the available databases.

回复收藏 0 原文

~没有更多了~