查找具有特定表的数据库或在 SQL Server 的每个数据库中查找表 [英] Find a database with a particular table OR Find a table in every database of SQL Server

问题描述

我有一个包含数百个数据库的 SQL Server，每个数据库都有数百个表.现在我想找到这些数据库中我要查找的表的位置.

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.

我可以使用

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 ?

推荐答案

好吧，如果您只想查找包含特定表的每个数据库，并且不打算查询该表，那么您可以做:

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

(如果您没有在数据库中使用多个模式，则不需要在 OBJECT_ID 调用中指定 dbo，否则我会使用它来避免在错误的模式中查找表)

(If you're not using multiple schemas in your databases, you won't need to specify dbo in the OBJECT_ID call, otherwise I use it to avoid finding tables in the wrong schema)

这篇关于查找具有特定表的数据库或在 SQL Server 的每个数据库中查找表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！