Как я перечисляю все таблицы во всех базах данных в SQL Server в единственном наборе результатов?

Я ищу код T-SQL для списка всех таблиц во всех базах данных в SQL Server (по крайней мере, в SS2005 и SS2008; было бы хорошо также относиться к SS2000). Выгода, однако, то, что я хотел бы единственный набор результатов. Это устраняет в других отношениях превосходный ответ от Pinal Dave:

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

Сохраненный proc вышеупомянутого генерирует один набор результатов для каждой базы данных, который прекрасен, если Вы находитесь в IDE как SSMS, который может отобразить несколько наборов результатов. Однако я хочу единственный набор результатов, потому что я хочу запрос, который является по существу инструментом "находки": если я добавляю пункт как WHERE tablename like '%accounts' затем это сказало бы мне, где найти мой BillAccounts, ClientAccounts и таблицы VendorAccounts, независимо от которой базы данных они находятся в.

20.05.2010 Обновление, приблизительно 20 минут спустя...

До сих пор ответ Remus выглядит самым интересным. Вместо того, чтобы отправлять это как ответ и присуждать его мне, я отправляю версию его здесь, что я изменил для включения имени DB и демонстрационного пункта фильтра. Похоже, что Remus получит кредит на ответ, тем не менее, в этой точке!

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;

24.05.2010 Обновление - Новый Лидер!

Обратная связь и ответы были большими. Длительное совместное участие привело к новому лидеру: ответ KM с 21 мая!

Вот проблемы, которые я раскрыл с решением Remus:

Главная проблема: у Пользователей есть различные полномочия, который приводит запрос успешно выполняться на основе данных (т.е. значение фильтрации). Работайте на моей производственной базе данных без фильтрации (т.е. исключение WHERE пункт) я получил эту ошибку на нескольких DBS, к которым у меня нет разрешения получить доступ:

Принципал сервера "msorens" не может получить доступ к базе данных "ETLprocDB" под текущим контекстом защиты.

Запрос успешно выполнится с некоторыми пунктами фильтрации - те, которые не касаются DBS вне моего уровня доступа.

Незначительная проблема: Не легко разлагаемый к поддержке SQL Server 2000 (да, существуют все еще некоторые из нас туда использование его...), потому что это создает единственную строку при накоплении записей для каждой базы данных. С моей системой я превзошел метку с 8000 символами приблизительно в 40 базах данных.

Незначительная проблема: Дублирующий код - установка цикла по существу копирует тело цикла. Я понимаю объяснение, но это - просто мой главный объект неприязни...

Ответ KM не сокрушен этими проблемами. Сохраненный proc 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