インスタンス内に存在する複数のデータベースに同一のＳＱＬを投げて、
SELECT結果を一挙に取得したいことが時々あります。
やり方をちょいちょい忘れるのでメモ。
USE master
/* 変数 */
DECLARE @dbname varchar(80);
DECLARE @SQL nvarchar(256);
CREATE TABLE #RESULT_TABLE
(
DBNAME nvarchar(80)
,HOGE_COL char(50)
)
/* ＤＢ一覧を取得するカーソル */
DECLARE DbNameList CURSOR FOR
SELECT NAME FROM sys.databases
WHERE NAME LIKE 'HOGEDB%'
OPEN DbNameList
/* ＤＢ名を取得 */
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
/* 次の調査対象となるＤＢ名を取得 */
FETCH NEXT FROM DbNameList INTO @dbname;
END;
/* お片付け */
SELECT * FROM #RESULT_TABLE
CLOSE DbNameList;
DEALLOCATE DbNameList;
DROP TABLE #RESULT_TABLE
GO
もっと効率のいいやり方や、綺麗な書き方もあるでしょうが、個人的用途ではこれで十分。
本当はテーブル値変数を使うつもりでしたが、動的SQLではテーブル値変数がうまく使えない？
みたいなので、一時テーブルを使っています。
転がっていたSQL Server2008R2 で確認しています。