布団が俺を呼んでいる

丘山大一のぶろぐ

インスタンス内に存在する複数のデータベースに同一のSQLを投げて結果を取得したい

インスタンス内に存在する複数のデータベースに同一のSQLを投げて、
SELECT結果を一挙に取得したいことが時々あります。
やり方をちょいちょい忘れるのでメモ。

USE master

/* 変数 */
DECLARE  @dbname varchar(80);
DECLARE @SQL nvarchar(256);
CREATE TABLE #RESULT_TABLE
(
DBNAME nvarchar(80)
,HOGE_COL char(50)
)

/* DB一覧を取得するカーソル */
DECLARE DbNameList CURSOR FOR
 SELECT NAME FROM sys.databases
 WHERE NAME LIKE 'HOGEDB%'

OPEN DbNameList

/* DB名を取得   */
FETCH NEXT FROM DbNameList INTO @dbname;

WHILE @@FETCH_STATUS = 0
 BEGIN

 /* 出力 SQLの中身は適当*/
 SET @SQL = 'INSERT INTO #RESULT_TABLE SELECT '  + '''' + @dbname + ''' AS DBNAME' + ' ,HOGE_COL FROM ' + @dbname + '.[dbo].' + 'HOGE_TABLE ;';
 EXECUTE sp_executesql @SQL

 /* 次の調査対象となるDB名を取得 */
 FETCH NEXT FROM DbNameList INTO @dbname;
END;

/* お片付け */
SELECT * FROM #RESULT_TABLE
CLOSE DbNameList;
DEALLOCATE DbNameList;
DROP TABLE #RESULT_TABLE

GO


もっと効率のいいやり方や、綺麗な書き方もあるでしょうが、個人的用途ではこれで十分。
本当はテーブル値変数を使うつもりでしたが、動的SQLではテーブル値変数がうまく使えない?
みたいなので、一時テーブルを使っています。
転がっていたSQL Server2008R2 で確認しています。

コメントを書く