Найти базу данных с определенной таблицей ИЛИ Найти таблицу в каждой базе данных SQL Server

У меня есть SQL Server с сотнями баз данных и каждой базой данных, имеющей сотни таблиц. Теперь я хотел бы найти, где в этих базах данных находится таблица, которую я ищу.

Я мог бы найти, существует ли таблица в отдельной базе данных, используя

use myDatabase 
select * from sys.tables  where name = 'mytable' 
GO

но используя это, я должен вручную изменить базу данных сотни раз. Я хотел бы найти только имя базы данных. Есть ли выход?

Ответ 1

Хорошо, если вы просто хотите найти каждую базу данных, содержащую определенную таблицу, и не собираетесь запрашивать таблицу, то вы можете просто сделать:

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

(Если вы не используете несколько схем в своих базах данных, вам не нужно указывать dbo в вызове OBJECT_ID, в противном случае я использую его, чтобы избежать поиска таблиц в неправильной схеме)

Ответ 2

Это должно делать то, что вы ищете:

EXEC sp_MSforeachdb "use [?];select * from sys.tables where name='TableName' "

Чтобы включить имя текущей базы данных в использовании, используйте:

EXEC sp_MSforeachdb "use [?];select '[?]' as DatabaseName, * from sys.tables where name='TableName' "

Ответ 3

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'

Ответ 4

Я знаю, что это старый поток, но был высоким в моем поиске в Google. Поэтому я хотел внести вклад для других, желающих найти в нем базу данных с определенной таблицей. Они применяются к SQL Server 2008 - Current.

Я начал с этого, который работал для моего входа в уровень SA, но дал мне проблемы с пользователями, у которых не было прав для всех баз данных.

SELECT name
FROM sys.databases
WHERE CASE
WHEN state_desc = 'ONLINE' THEN OBJECT_ID( QUOTENAME( name ) + '.[dbo].[mytablename]','U' )
END IS NOT NULL;

Но в итоге это добавило ограничение HAS_DBACCESS(name) = 1, чтобы запрос не сбой с ошибкой безопасности.

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;

Ответ 5

exec sp_msforeachdb @command1='     
USE ?;
select * from sys.tables where name = ''CLIENTS'''

Ответ 6

exec 'select ''?'', name from [?].sys.tables where name = ''yourTable'''