如何在单个结果集中列出SQL Server所有数据库中的所有表? [英] How do I list all tables in all databases in SQL Server in a single result set?
问题描述
我正在寻找T-SQL代码以列出SQL Server中所有数据库的所有表(至少在SS2005和SS2008中；也可以将其应用于SS2000).但是要注意的是，我需要一个 单个结果集 .这排除了 上面存储的proc会生成一个结果集 每个数据库 ，如果您位于可显示多个结果集的类似SSMS的IDE中，这很好.但是，我只需要一个结果集，因为我想要一个实质上是查找"工具的查询:如果添加类似 到目前为止，Remus的回答看起来最有趣.我没有在此发布答案并将其奖励给自己，而是在此处发布了对其进行了修改的版本，并对其进行了修改，以包括数据库名称和示例过滤子句.看来Remus会得到答案的功劳！
反馈和答复都很棒.持续的协作参与导致了新的领跑者:KM从5月21日开始回答！ 以下是我在Remus解决方案中发现的问题: 主要问题::用户具有不同的权限，这些权限会导致基于数据(即过滤值)的查询成功.使用 no 过滤在生产数据库上运行(即省略 服务器主体"msorens"无法访问数据库
在当前安全上下文下为"ETLprocDB". 查询 将会 成功，并带有一些过滤子句-那些不会触及我访问级别之外的数据库的子句. 次要问题::不容易降级到SQL Server 2000支持(是的，我们当中仍有一些人正在使用它...)，因为它在累积每个数据库的条目时会生成一个字符串.使用我的系统，我在大约40个数据库中超过了8000个字符. 次要问题:代码重复-循环设置本质上是复制循环主体.我了解其基本原理，但这只是我的宠儿…… KM的答案不受这些问题的困扰.存储的proc 接下来，我将根据我的个人喜好发布对KM答案的修改.具体来说: 这是我对KM代码的修改(仅对表名应用了示例过滤器):
有关获取服务器上所有表的简单方法，请尝试以下操作: 它将返回包含服务器+数据库+模式+表名的单列:
样本输出: 如果您使用的不是SQL Server 2005或更高版本，请将 编辑 将所有表的@Search设置为NULL，将其设置为'dbo.users'或'users'或'.master.dbo'之类，甚至包括通配符，例如'.master.％.u'等. /p> I am looking for T-SQL code to list all tables in all databases in SQL Server (at least in SS2005 and SS2008; would be nice to also apply to SS2000). The catch, however, is that I would like a single result set. This precludes the otherwise excellent answer from Pinal Dave: The above stored proc generates one result set per database, which is fine if you are in an IDE like SSMS that can display multiple result sets. However, I want a single result set because I want a query that is essentially a "find" tool: if I add a clause like So far, Remus' answer looks most interesting. Rather than post this as an answer and award it to myself, I am posting a version of it here that I have modified to include the DB name and a sample filter clause. It is looking like Remus will get the credit for the answer, though, at this point!
The feedback and answers have been great. Continued collaborative participation has led to a new frontrunner: KM's answer from May 21! Here are the issues I uncovered with Remus' solution: Major issue: Users have different permissions which leads the query to succeed based on the data (i.e. the filtering value). Run on my production database with no filtering (i.e. omitting the The server principal "msorens" is not able to access the database
"ETLprocDB" under the current security context. The query will succeed with some filtering clauses--those that do not touch the DBs outside my access level. Minor issue: Not easily degradable to SQL Server 2000 support (yes, there are still some of us out there using it...) because it builds a single string while accumulating entries for each database. With my system, I surpassed the 8000-character mark at around 40 databases. Minor issue: Duplicate code--the loop setup essentially duplicates the loop body. I understand the rationale but it is just a pet peeve of mine... KM's answer is not afflicted by these issues. The stored proc I am posting next my modifications to KM's answer based on my personal preferences. Specifically: Here is my modification to KM's code (with a sample filter applied just to the table name):
for a simple way to get all tables on the server, try this: it will return a single column that contains the server+database+schema+table name:
sample output: if you are not on SQL Server 2005 or up, replace the EDIT set @Search to NULL for all tables, set it to things like 'dbo.users' or 'users' or '.master.dbo' or even include wildcards like '.master.%.u', etc. 这篇关于如何在单个结果集中列出SQL Server所有数据库中的所有表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！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更新-新的领跑者！
WHERE子句)，我在没有访问权限的多个DB上收到此错误:
sp_msforeachdb考虑了用户的权限，因此避免了权限问题.我尚未在SS2000上尝试过该代码，但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
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
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
sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'
WHERE tablename like '%accounts' then it would tell me where to find my BillAccounts, ClientAccounts, and VendorAccounts tables regardless of which database they reside in.
2010.05.20 Update, about 20 minutes later...
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 Update -- New Front runner!
WHERE clause) I received this error on several DBs that I do not have permission to access:
sp_msforeachdb takes into account the user's permissions so it avoids permission problems. I have not yet tried the code with SS2000 but KM indicates the adjustments that should do it.
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
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
DECLARE @AllTables table with CREATE TABLE #AllTables and then every @AllTables with #AllTables and it will work.
here is a version that will allow a search parameter to be used on any part or parts of the server+database+schema+table names: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
