全DBに対して同一のクエリを実行させたいときに少しハマったので、その内容と解決方法について書いておきます。
実現のために考えた手順は以下の通りです。
- データベース名リストを取得
- ループで回しつつ、use [DB名]を実行してDB切り替え後に実行したいクエリを発行
というシンプルなものですが、最初に考えたコードだとうまくいきませんでした。
うまくいかなかった例
DECLARE
@Database sysname
,@sql nvarchar(2000)
DECLARE
CMyCursor CURSOR FAST_FORWARD FOR
SELECT
name
FROM
master.sys.databases WITH(NOLOCK)
WHERE
-- システムDBおよびディストリビューションDB除外、かつオンラインのDBに限定
Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0
AND state = 0
OPEN CMyCursor
FETCH NEXT FROM CMyCursor INTO
@Database
-- DB単位のループ
WHILE @@fetch_status = 0
BEGIN
set @sql = 'USE ' + CAST(@Database AS NVARCHAR(100)) + ';'
execute sp_executesql @sql
--DBごとに関数やストアドリストを出力したい
SELECT
ROUTINE_NAME,
ROUTINE_TYPE,
OBJECT_DEFINITION(object_id(ROUTINE_NAME)) as FullDefinition
FROM
INFORMATION_SCHEMA.ROUTINES
FETCH NEXT FROM CMyCursor INTO
@Database
END
CLOSE CMyCursor
DEALLOCATE CMyCursor
これだと、USEが効きませんでした。
正確にいうと、sp_executesqlは実行完了後にコンテキストが元に戻るため、USE [DB名]自体はちゃんと実行されるのですが、実行完了後に現在選択中のDBに戻ってからクエリを発行するので、結局同じDBに対してループの数だけ同じクエリを発行し続けることになります。
改良後のクエリ
DECLARE
@Database sysname
,@sql nvarchar(2000)
DECLARE
CMyCursor CURSOR FAST_FORWARD FOR
SELECT
name
FROM
master.sys.databases WITH(NOLOCK)
WHERE
-- システムDBおよびディストリビューションDB除外、かつオンラインのDBに限定
Cast(CASE WHEN name IN ('master', 'model', 'msdb', 'tempdb') THEN 1 ELSE is_distributor END As bit) = 0
AND state = 0
OPEN CMyCursor
FETCH NEXT FROM CMyCursor INTO
@Database
-- DB単位のループ
WHILE @@fetch_status = 0
BEGIN
set @sql = ''
-- useを使う必要があるが、use単体でexecuteすると実行後にコンテキストが現在のDBに戻ってしまう。そのため丸ごと動的SQLで実行する
set @sql = @sql + ' USE ' + CAST(@Database AS NVARCHAR(100)) + ';'
-- ここにDBごとに実行したい処理を書く
set @sql = @sql + ' SELECT '
set @sql = @sql + ' ROUTINE_NAME, '
set @sql = @sql + ' ROUTINE_TYPE, '
set @sql = @sql + ' OBJECT_DEFINITION(object_id(ROUTINE_NAME)) as FullDefinition '
set @sql = @sql + ' FROM '
set @sql = @sql + ' INFORMATION_SCHEMA.ROUTINES '
-- useと実行したい処理を同一コンテキスト内で実行
execute sp_executesql @sql
FETCH NEXT FROM CMyCursor INTO
@Database
END
CLOSE CMyCursor
DEALLOCATE CMyCursor
動的SQLの中に、USEと実行したいクエリとを含めることで、同一コンテキスト内での実行が可能となり、各DBに対してのクエリ発行という意図した結果を得ることができました。
参考:
[How can I get a list of all of the user databases via t-sql?]
https://stackoverflow.com/questions/463118/how-can-i-get-a-list-of-all-of-the-user-databases-via-t-sql?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa
[Database USE issues with dynamic SQL]
https://ask.sqlservercentral.com/questions/5108/database-use-issues-with-dynamic-sql.html