SQL Serverのすべてのデータベースのすべてのテーブルを1つの結果セットにリストアップするにはどうすればよいですか？

質問

私は、SQL Server (少なくとも SS2005 と SS2008 で。SS2000 にも適用できるとよいのですが) のすべてのデータベースのすべてのテーブルを一覧表示する T-SQL コードを探しています。ただし、私が欲しいのは 単一の結果セット . このため ピナル・デイヴ :

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

上記のストアドプロックは1つの結果セットを生成します データベースごとに これは、複数の結果セットを表示できる SSMS のような IDE を使用している場合は問題ありません。しかし、私は本質的に "find"ツールであるクエリが欲しいので、単一の結果セットが欲しいのです：私が以下のような句を追加すると WHERE tablename like '%accounts' のような句を追加すると、どのデータベースに存在するかにかかわらず、BillAccounts、ClientAccounts、および VendorAccounts テーブルを見つける場所を教えてくれます。

2010.05.20 更新、約 20 分後...

今のところ、リーマスの回答が最も面白そうです。これを回答として投稿して自分に賞を与えるよりも、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;

2010.05.24追記 -- 新フロントランナー登場!

フィードバックと回答は素晴らしいものでした。継続的な共同参加により 新しいフロントランナー : 5月21日のKMの答え!

以下は、私が発見したRemusの解決策に関する問題点です。

大きな問題です。 ユーザーには異なる権限があり、データ（すなわちフィルタリング値）に基づいてクエリが成功するようになります。私の本番データベースで いいえ フィルタリング（すなわち WHERE 節を削除) アクセス権限のない複数のDBでこのエラーが発生しました。

サーバプリンシパル "msorens" は、現在のセキュリティコンテキストでは、データベース "ETLprocDB"現在のセキュリティ コンテキストではアクセスできません。

クエリ は は、私のアクセスレベル外のDBに触れないものであれば、いくつかのフィルタリング節で成功します。

マイナーな問題です。 データベースごとにエントリを蓄積しながら1つの文字列を構築するため、SQL Server 2000対応（そう、まだ使っている人がいるんだ...）に簡単にデグレードしない。私のシステムでは、約40のデータベースで8000文字を超えました。

マイナーな問題です。 ループのセットアップが本質的にループ本体を重複している。私は論理的根拠を理解していますが、それは単に私の嫌いなところです...。

KM の回答はこれらの問題には悩まされていません。ストアド プロシージャは sp_msforeachdb はユーザーのパーミッションを考慮するので、パーミッションの問題を回避することができます。私はまだSS2000でこのコードを試していませんが、KMはそれを行うべき調整を示しています。

次に、KM さんの回答に対して、私の個人的な好みに基づいて修正したものを投稿します。具体的には

• サーバー名は結果セットに何も追加しないので、削除しました。
• 名前コンポーネントを結果セット内の独自のフィールドに分割しました (データベース名、スキーマ名、およびテーブル名)。
• 3 つのフィールドのそれぞれについて、個別のフィルターを導入しました。
• 3つのフィールドによるソートを追加しました（好みに応じて変更可能です）。

以下は、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

を実行すると、サーバ名+データベース名+スキーマ名+テーブル名を含む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 と入力すれば動作します。

EDIT

サーバー名、データベース名、スキーマ名、テーブル名の一部または全部を検索パラメータとして使用できるようにするバージョンです。

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

をすべてのテーブルで NULL に設定し、'dbo.users' または 'users' または '.master.dbo' などに設定し、さらに '.master.%.u' などのワイルドカードも含めて設定します。