Я ищу код T-SQL для отображения всех таблиц во всех базах данных в SQL Server (по крайней мере, в SS2005 и SS2008, было бы неплохо также примениться к SS2000). Однако улов состоит в том, что мне нужен одиночный результирующий набор. Это исключает отличный ответ от Pinal Dave:
sp_msforeachdb 'select "?" AS db, * from [?].sys.tables'
Вышеупомянутая сохраненная передача генерирует один набор результатов для каждой базы данных, что прекрасно, если вы находитесь в среде IDE, такой как SSMS, которая может отображать несколько наборов результатов. Тем не менее, мне нужен единственный набор результатов, потому что я хочу, чтобы запрос был по существу инструментом "найти": если я добавлю предложение типа 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 Обновление - Новый передний бегун!
Отзывы и ответы были замечательными. Продолжающееся совместное участие привело к новому лидеру: ответ KM с 21 мая!
Вот проблемы, которые я обнаружил с помощью решения Ремуса:
Основная проблема: У пользователей разные разрешения, которые приводят к успешному выполнению запроса на основе данных (то есть значения фильтрации). Запустите в моей производственной базе данных фильтрацию нет (т.е. Опустив предложение WHERE
). Я получил эту ошибку на нескольких БД, у которых у меня нет доступа к доступу:
Принцип сервера msorens не может получить доступ к базе данных "ETLprocDB" в контексте текущей безопасности.
Запрос будет успешным с некоторыми предложениями фильтрации - теми, которые не касаются БД вне моего уровня доступа.
Незначительная проблема: Нелегко разлагаться на поддержку SQL Server 2000 (да, есть некоторые из нас там, используя ее...), потому что она строит одну строку при накоплении записей для каждой базы данных, С моей системой я превзошел знак 8000 символов в 40 базах данных.
Незначительная проблема: Дублирующий код - настройка цикла по существу дублирует тело цикла. Я понимаю это обоснование, но это всего лишь домашнее животное...
Ответ KM не огорчен этими проблемами. Хранимый proc sp_msforeachdb
учитывает разрешения пользователя, чтобы избежать проблем с разрешением. Я еще не пробовал код с SS2000, но KM указывает настройки, которые должны это сделать.
Я отправляю следующие изменения в ответ KM, основываясь на моих личных предпочтениях. В частности:
- Я удалил имя сервера, поскольку он ничего не добавляет в результирующий набор.
- Я разделил компоненты имени в свои собственные поля в наборе результатов (имя db, имя схемы и имя таблицы).
- Я ввел отдельные фильтры для каждого из трех полей.
- Я добавил сортировку по трем полям (которые могут быть изменены в соответствии с вашими предпочтениями).
Вот моя модификация кода 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 '''[email protected]+''' AND s.name LIKE '''[email protected]+''' AND t.name LIKE '''[email protected]+''''
INSERT INTO @AllTables (DbName, SchemaName, TableName)
EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY DbName, SchemaName, TableName