Я ищу код 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, независимо от которой базы данных они находятся в.
До сих пор ответ 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;
Обратная связь и ответы были большими. Длительное совместное участие привело к новому лидеру: ответ 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
для простого способа получить все таблицы на сервере, попробуйте следующее:
SET NOCOUNT ON
DECLARE @AllTables table (CompleteTableName nvarchar(4000))
INSERT INTO @AllTables (CompleteTableName)
EXEC sp_msforeachdb 'select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id'
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY 1
он вернет один столбец, содержащий сервер + база данных + схема + имя таблицы: пример вывода:
CompleteTableName
--------------------------------------------
YourServer.YourDatabase1.YourSchema1.YourTable1
YourServer.YourDatabase1.YourSchema1.YourTable2
YourServer.YourDatabase1.YourSchema2.YourTable1
YourServer.YourDatabase1.YourSchema2.YourTable2
YourServer.YourDatabase2.YourSchema1.YourTable1
, если вы не используете SQL Server 2005 или более поздней версии, замените DECLARE @AllTables table
на CREATE TABLE #AllTables
, а затем каждые @AllTables
] с помощью #AllTables
, и он будет работать.
РЕДАКТИРОВАТЬ
вот версия, которая позволит использовать параметр поиска в любой части или частях сервера + база данных + схема + имена таблиц:
SET NOCOUNT ON
DECLARE @AllTables table (CompleteTableName nvarchar(4000))
DECLARE @Search nvarchar(4000)
,@SQL nvarchar(4000)
SET @Search=null --all rows
SET @SQL='select @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name from [?].sys.tables t inner join sys.schemas s on t.schema_id=s.schema_id WHERE @@SERVERNAME+''.''+''?''+''.''+s.name+''.''+t.name LIKE ''%'+ISNULL(@SEARCH,'')+'%'''
INSERT INTO @AllTables (CompleteTableName)
EXEC sp_msforeachdb @SQL
SET NOCOUNT OFF
SELECT * FROM @AllTables ORDER BY 1
установите @Search в NULL для всех таблиц, установите его в такие вещи, как 'dbo.users', 'users' или '.master.dbo', или даже включать подстановочные знаки, такие как '.master.%. u' и т. д.
declare @sql nvarchar(max);
set @sql = N'select cast(''master'' as sysname) as db_name, name collate Latin1_General_CI_AI, object_id, schema_id, cast(1 as int) as database_id from master.sys.tables ';
select @sql = @sql + N' union all select ' + quotename(name,'''')+ ', name collate Latin1_General_CI_AI, object_id, schema_id, ' + cast(database_id as nvarchar(10)) + N' from ' + quotename(name) + N'.sys.tables'
from sys.databases where database_id > 1
and state = 0
and user_access = 0;
exec sp_executesql @sql;
Я почти уверен, что вам придется пройтись по списку баз данных, а затем перечислить каждую таблицу. Вы должны суметь объединить их вместе.
Все, что вам нужно сделать, это запустить хранимую процедуру sp_tables. http://msdn.microsoft.com/en-us/library/aa260318 (SQL.80) .aspx
Некоторое время назад я опубликовал ответ здесь , который вы могли бы использовать здесь. Схема такова: