全てのユーザーデータベースに同一クエリを実行する

はじめに

SQLServerにて全てのデータベースを対象にクエリを実施したいといった場合が稀にあるかと思います。
あらかじめデータベース名が全てわかっていればクエリを準備しておくことができますが、この先データベースが増える、もしくは減る場合があるときに同一クエリをそのまま使いまわすことができません。

ですので、本ブログでは全てのデータベースに対してテーブルを作成する方法を検証します

 

 

検証図

以下の図のように今回はユーザDB全てを対象に実施します


検証環境はたまたまSQLMIを検証で使用していたため
これを代用したいと思います
また、クエリの実行はSSMS(SQL Server Management Studio)を使用します

事前準備

まずは、test1,2のデータベースを作成し
このデータベースにalldbtableというテーブルがないことを確認します

なお、DBの作成方法等は省略します
(ユーザ作成)テーブル一覧は以下のクエリで取得できます

USE [<DB名>]
select * from sys.objects where type = 'U';

テーブルを作成する

以下のクエリを実行します

DECLARE @dbname nvarchar(50)
DECLARE @tablecreate nvarchar(max)

DECLARE dbname CURSOR FOR
	SELECT name
	FROM sys.databases
	where name <> 'master'
	and name <> 'tempdb'
	and name <> 'model'
	and name <> 'msdb'

OPEN dbname;
 
FETCH NEXT FROM dbname 
INTO @dbname;


WHILE @@FETCH_STATUS = 0
BEGIN

	SET @tablecreate =
    N'USE[' + @dbname + ']
	CREATE TABLE alldbtable (name nchar(10));
	'
	EXEC( @tablecreate)

	
  FETCH NEXT FROM dbname INTO @dbname;

END

CLOSE dbname
DEALLOCATE dbname

簡単に説明をしますと


DECLARE dbname CURSOR FOR
	SELECT name
	FROM sys.databases
	where name <> 'master'
	and name <> 'tempdb'
	and name <> 'model'
	and name <> 'msdb'

にてユーザDBを取得しています

OPEN dbname;
FETCH NEXT FROM dbname 
INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
・・・・・
END
CLOSE dbname
DEALLOCATE dbname

にてBEGIN~ENDの中でdbname内に各DB名を入れ込んで使用できるようにしています
BEGIN~ENDの処理内で、一旦変数の中にクエリ分を入れ込んでEXECUTEにて実行しているのは、
クエリの実行完了後に使用するDBが実行元のmasterDBに戻ってしまうので
まとめて実行できるようにクエリを別に定義して実行を行っています
ですので、実行するクエリ文を変える場合は

CREATE TABLE alldbtable (name nchar(10));

の部分を変更してください
(N’USE[‘ + @dbname + ‘]は消さないでください)

確認する

先ほどのテーブル確認クエリを実施すると
しっかりと表示されているのでテーブルが作成されていることが確認できました


終わりに

以上で全てのユーザデータベースに同一内容のクエリを適用することができました
クエリの作成はN”(シングルクォーテーション)で囲む必要があるので
長文になると気をつかうことになりますが汎用的なクエリを作成できるので
試してみてはいかがでしょうか。

いいね (←参考になった場合はハートマークを押して評価お願いします)
読み込み中...

注意事項・免責事項

※技術情報につきましては投稿日時点の情報となります。投稿日以降に仕様等が変更されていることがありますのでご了承ください。

※公式な技術情報の紹介の他、当社による検証結果および経験に基づく独自の見解が含まれている場合がございます。

※これらの技術情報によって被ったいかなる損害についても、当社は一切責任を負わないものといたします。十分な確認・検証の上、ご活用お願いたします。

※当サイトはマイクロソフト社によるサポートページではございません。パーソルクロステクノロジー株式会社が運営しているサイトのため、マイクロソフト社によるサポートを希望される方は適切な問い合わせ先にご確認ください。
 【重要】マイクロソフト社のサポートをお求めの方は、問い合わせ窓口をご確認ください