查找具有特定表的数据库或在 SQL Server 的每个数据库中查找表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4733170/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find a database with a particular table OR Find a table in every database of SQL Server
提问by Thunder
I have a SQL Server with hundreds of databases and each database having hundreds of tables. Now I would like to find where in these databases is a table that I am looking for.
我有一个包含数百个数据库的 SQL Server，每个数据库都有数百个表。现在我想在这些数据库中找到我正在寻找的表的位置。
I could find if a table existed in individual database using
我可以使用以下方法找到单个数据库中是否存在表
use myDatabase
select * from sys.tables where name = 'mytable'
GO
but using this means I have to manually change the database for hundreds of times . I would like to find the database name only. Is there a way out ?
但使用这意味着我必须手动更改数据库数百次。我只想找到数据库名称。有出路吗 ？
回答by Damien_The_Unbeliever
Okay, if you're just wanting to find each database that contains a particular table, and aren't going to be querying the table, then you can just do:
好的，如果您只想查找包含特定表的每个数据库，并且不打算查询该表，那么您可以这样做：
create table #t (
DBName sysname not null
)
go
exec sp_MSforeachdb 'use [?]; if OBJECT_ID(''dbo.mytable'') is not null insert into #t (DBName) select ''?'''
go
select * from #t
go
drop table #t
(If you're not using multiple schemas in your databases, you won't need to specify dbo in the OBJECT_IDcall, otherwise I use it to avoid finding tables in the wrong schema)
（如果您没有在数据库中使用多个模式，则不需要在OBJECT_ID调用中指定 dbo ，否则我会使用它来避免在错误的模式中查找表）
回答by John Sansom
This should do what you are looking for:
这应该做你正在寻找的：
EXEC sp_MSforeachdb "use [?];select * from sys.tables where name='TableName' "
To include the name of the current database in the output use:
要在输出中包含当前数据库的名称，请使用：
EXEC sp_MSforeachdb "use [?];select '[?]' as DatabaseName, * from sys.tables where name='TableName' "
回答by Martin Smith
SELECT DISTINCT DB_NAME(database_id)
FROM [sys].[dm_db_index_operational_stats](NULL,NULL,NULL,NULL)
WHERE OBJECT_NAME(object_id,database_id) = 'mytable'
回答by Joe McDonald
I know this is an old thread but was high on my google search. So I wanted to contribute for others looking to find a database with a certain table in it. These apply to SQL Server 2008 - Current.
我知道这是一个旧线程，但在我的谷歌搜索中很高。所以我想为其他希望找到包含某个表的数据库的人做出贡献。这些适用于 SQL Server 2008 - 当前。
I started with this, which worked for my SA level login, but gave me issues with users that did not have permissions to all databases.
我从这个开始，它适用于我的 SA 级别登录，但给我带来了没有所有数据库权限的用户的问题。
SELECT name
FROM sys.databases
WHERE CASE
WHEN state_desc = 'ONLINE' THEN OBJECT_ID( QUOTENAME( name ) + '.[dbo].[mytablename]','U' )
END IS NOT NULL;
But ended up with this adding the HAS_DBACCESS(name) = 1in restriction so that the query would not fail with a security error.
但最终添加了HAS_DBACCESS(name) = 1in 限制，以便查询不会因安全错误而失败。
SELECT name
FROM sys.databases
WHERE HAS_DBACCESS(name) = 1 and
CASE
WHEN state_desc = 'ONLINE' THEN OBJECT_ID( QUOTENAME( name ) + '.[dbo].[mytablename]','U' )
END IS NOT NULL;
回答by Jonathan
exec sp_msforeachdb @command1='
USE ?;
select * from sys.tables where name = ''CLIENTS'''
回答by nzrytmn
this is also one of the way, similar with solution of @Jonathan :
这也是一种方式，类似于@Jonathan 的解决方案：
exec sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%YourTableName%'''
回答by Ben Thul
exec 'select ''?'', name from [?].sys.tables where name = ''yourTable'''
