sql-server - 如何在单个结果集中列出 SQL Server 中所有数据库中的所有表？

Question

我正在寻找 T-SQL 代码来列出 SQL Server 中所有数据库中的所有表(至少在 SS2005 和 SS2008 中；如果也适用于 SS2000 就好了)。然而，问题是我想要一个单个结果集。这排除了 Pinal Dave 的其他优秀答案。 :

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'

上面的存储过程每个数据库生成一个结果集，如果您使用的是像 SSMS 这样可以显示多个结果集的 IDE，那么这很好。但是，我想要一个结果集，因为我想要一个本质上是“查找”工具的查询:如果我添加像 WHERE tablename like '%accounts' 这样的子句，那么它会告诉我在哪里查找我的 BillAccounts、ClientAccounts 和 VendorAccounts 表，无论它们驻留在哪个数据库中。

<小时/>

2010.05.20更新，大约20分钟后...

到目前为止，莱姆斯的回答看起来最有趣。我没有将其作为答案发布并将其奖励给自己，而是在这里发布了一个版本，我已对其进行了修改以包含数据库名称和示例过滤器子句。不过，目前看来莱姆斯将获得答案!

declare @sql nvarchar(max);
set @sql = N'select b.name as "DB", a.name collate Latin1_General_CI_AI as "Table", object_id, schema_id, cast(1 as int) as database_id  from master.sys.tables a join sys.databases b on database_id=1 where a.name like ''account%''';

select @sql = @sql + N' union all select b.name as "DB", a.name collate Latin1_General_CI_AI, object_id, schema_id, ' + cast(database_id as nvarchar(10)) + N' from ' + quotename(name) + N'.sys.tables a join sys.databases b on database_id=' + cast(database_id as nvarchar(10)) + 'where a.name like ''account%'''
from sys.databases where database_id > 1 

and state = 0
and user_access = 0;

exec sp_executesql @sql;

<小时/>

2010.05.24 更新——新的领跑者!

反馈和答案非常好。持续的协作参与催生了一个新的领跑者:KM 从 5 月 21 日起的答复!

以下是我通过 Remus 解决方案发现的问题:

主要问题:用户拥有不同的权限，导致根据数据(即过滤值)查询是否成功。在我的生产数据库上运行，无过滤(即省略 WHERE 子句) 我在几个我无权访问的数据库上收到此错误访问:

The server principal "msorens" is not able to access the database"ETLprocDB" under the current security context.

查询将通过一些过滤子句成功，这些子句不会触及我的访问级别之外的数据库。

小问题:不容易降级到 SQL Server 2000 支持(是的，我们中仍然有一些人在使用它...)，因为它在为每个数据库累积条目的同时构建单个字符串。通过我的系统，我在大约 40 个数据库中超过了 8000 个字符的大关。

小问题:重复代码——循环设置本质上重复了循环体。我理解其中的原理，但这只是我的一个小烦恼......

KM 的回答不受这些问题的影响。存储过程sp_msforeachdb考虑了用户的权限，因此避免了权限问题。我还没有尝试使用 SS2000 的代码，但 KM 指示了应该进行的调整。

接下来我将根据我的个人喜好发布对 KM 答案的修改。具体来说:

• 我已删除服务器名称，因为它实际上并未在结果集中添加任何内容。
• 我已将名称组件拆分为结果集中各自的字段(数据库名称、架构名称和表名称)。
• 我为这三个字段中的每一个字段引入了单独的过滤器。
• 我添加了按三个字段排序(可以根据您的喜好进行修改)。

这是我对 KM 代码的修改(仅将示例过滤器应用于表名称):

SET NOCOUNT ON
DECLARE @AllTables table (DbName sysname,SchemaName sysname, TableName sysname)
DECLARE
     @SearchDb nvarchar(200)
    ,@SearchSchema nvarchar(200)
    ,@SearchTable nvarchar(200)
    ,@SQL nvarchar(4000)
SET @SearchDb='%'
SET @SearchSchema='%'
SET @SearchTable='%Account%'
SET @SQL='select ''?'' as DbName, s.name as SchemaName, t.name as TableName from [?].sys.tables t inner join [?].sys.schemas s on t.schema_id=s.schema_id WHERE ''?'' LIKE '''+@SearchDb+''' AND s.name LIKE '''+@SearchSchema+''' AND t.name LIKE '''+@SearchTable+''''

INSERT INTO @AllTables (DbName, SchemaName, TableName)
    EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY DbName, SchemaName, TableName

Answer 1

要获取服务器上所有表的简单方法，请尝试以下操作:

SET NOCOUNT ON
DECLARE @AllTables table (CompleteTableName nvarchar(4000))
INSERT INTO @AllTables (CompleteTableName)
    EXEC sp_msforeachdb 'select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id'
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY 1

它将返回包含服务器+数据库+架构+表名称的单个列:示例输出:

CompleteTableName
--------------------------------------------
YourServer.YourDatabase1.YourSchema1.YourTable1
YourServer.YourDatabase1.YourSchema1.YourTable2
YourServer.YourDatabase1.YourSchema2.YourTable1
YourServer.YourDatabase1.YourSchema2.YourTable2
YourServer.YourDatabase2.YourSchema1.YourTable1

如果您使用的不是 SQL Server 2005 或更高版本，请将 DECLARE @AllTables table 替换为 CREATE TABLE #AllTables，然后替换每个 @AllTables > 与 #AllTables 一起使用即可。

编辑
这是一个允许在服务器+数据库+模式+表名称的任何部分上使用搜索参数的版本:

SET NOCOUNT ON
DECLARE @AllTables table (CompleteTableName nvarchar(4000))
DECLARE @Search nvarchar(4000)
       ,@SQL   nvarchar(4000)
SET @Search=null --all rows
SET @SQL='select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id WHERE @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name LIKE ''%'+ISNULL(@SEARCH,'')+'%'''

INSERT INTO @AllTables (CompleteTableName)
    EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY 1

将所有表的@Search设置为NULL，将其设置为“dbo.users”或“users”或“.master.dbo”，甚至包括“.master.%.u”等通配符