LoginSignup
7

More than 5 years have passed since last update.

SQLServer: 各DB上で同一の処理を実行させる方法

Last updated at Posted at 2018-05-14

全DBに対して同一のクエリを実行させたいときに少しハマったので、その内容と解決方法について書いておきます。
実現のために考えた手順は以下の通りです。

  1. データベース名リストを取得
  2. ループで回しつつ、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

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
7