将数据库名称与表列表关联 [英] Associate Database Name with Table List

问题描述

列出服务器上具有架构的所有表都是没有问题的

It is no problem to list all tables with schemas on a server

SELECT SCHEMA_NAME(schema_id), name FROM sys.tables

如何确定表驻留在哪个数据库中?

How can I determine which database the tables reside in ?

推荐答案

sys.tables存在于所有数据库中，因此我没有注意到您不知道所使用的数据库的事实.可以运行DB_NAME(DB_ID())获取数据库名称

sys.tables exists in all databases so I am not following the fact that you don't know the db you are in. you can run DB_NAME(DB_ID()) to get the db name

SELECT  DB_NAME(DB_ID()),SCHEMA_NAME(schema_id), name FROM sys.tables

但是在这种情况下，DB_NAME(DB_ID())将为每一行返回相同的值

but in this case DB_NAME(DB_ID()) will return the same value for every row

要针对所有数据库执行此操作，您可以执行此操作

to do it for all database, you can do this

EXEC sp_msforeachdb 'use  [?] SELECT  ''?'',SCHEMA_NAME(schema_id), name 
                                  FROM sys.tables'

您当然也可以将其转储到表中

You can of course dump it into a table as well

CREATE TABLE #output (DatabaseName VARCHAR(1000), 
                   SchemaName VARCHAR(1000), 
                  TableName VARCHAR(1000))

INSERT #output
EXEC sp_msforeachdb 'use  [?] SELECT  ''?'',SCHEMA_NAME(schema_id), name 
                                  FROM sys.tables'

SELECT * FROM #output

就像仅供参考，sp_msforeachdb proc未记录在案，您不应将其用于生产代码，以便快速找到有用的东西，以使生产代码滚动使用您自己的此proc版本.

Just as a FYI, the sp_msforeachdb proc is undocumented and you should not use it for production code, to quickly find something is fine, for production code roll your own version of this proc

另请参阅此处的亚伦·伯特兰德(Aaron Bertrand)的帖子:

See also Aaron Bertrand's posts here:

• 使可靠性和灵活性更高sp_MSforeachdb
• 查看全文