この記事は更新から24ヶ月以上経過しているため、最新の情報を別途確認することを推奨いたします。

はじめに

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

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

 

 

検証図

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

事前準備

まずは、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”（シングルクォーテーション）で囲む必要があるので
長文になると気をつかうことになりますが汎用的なクエリを作成できるので
試してみてはいかがでしょうか。