別スキーマのテーブルをSELECT * で参照するビュー（VIEW）を自動作成するストアドプロシージャ [SQLServer]

複数のスキーマを用意している環境で、あるスキーマのテーブルを別のスキーマから参照するビューを作成して使用することがあります。

今回は、別のスキーマのテーブルをテーブルのように参照するビューを自動生成するストアドプロシージャを作成します。

1 SQLServerのスキーマとは
2 別スキーマのテーブルを参照するビュー
3 別スキーマのテーブルをSELECT * で参照するビューを自動作成するストアドプロシージャ
- 3.1 スキーマとテーブルの作成
- 3.2 ストアドプロシージャが使用するテーブルの準備

SQLServerのスキーマとは

別スキーマのテーブルを参照するビュー（VIEW）を自動作成するストアドプロシージャを作成する前に、スキーマについて簡単に記述しておきます。

SQLServerにはスキーマ（Schema）というオブジェクトが用意されています。

スキーマとは、テーブルなどのデータベース内のオブジェクトをグループ化するコンテナの名前（名前空間）です。
スキーマには権限などが設定できるので、特定のオブジェクトの権限を制御することができます。
スキーマを使用することで、ユーザーとは別の権限管理が可能になります。

スキーマはオブジェクトをグループ化する名前空間なので、スキーマが違えば同じ名前のオブジェクトを作成することができます。（1つのスキーマ内には同じ名前のオブジェクトを作成できませんが、スキーマが違えば作成できます。）

別スキーマのテーブルを参照するビュー

別スキーマのテーブルを参照するビューは、スキーマが違うテーブルのデータをテーブルのように参照するビューです。
具体的には、SELECT * FROM スキーマ名.テーブル名で別スキーマのテーブルのデータをすべて抽出するビューです。

例えば、abcというスキーマとdefというスキーマがあるとします。
abcスキーマには、TEST_TABLEという名前のテーブルを作成します。

CREATE TABLE abc.TEST_TABLE
(
    COL_1 int PRIMARY KEY,
    COL_2 nvarchar(20)
);

CREATE TABLE abc.TEST_TABLE

(

COL_1 int PRIMARY KEY,

COL_2 nvarchar(20)

);

このテーブルを別のスキーマであるdefスキーマから参照するには、以下のようなビューを作成します。

CREATE VIEW def.TEST_TABLE
AS
SELECT * FROM abc.TEST_TABLE;

CREATE VIEW def.TEST_TABLE

SELECT * FROM abc.TEST_TABLE;

上記のような別スキーマのテーブルを参照するビューを作成する必要がある場合、テーブルの数が多くなればなるほど同じようなSQLをたくさん記述しなければなりません。
そこで、今回はそれを自動的に作成するためのストアドプロシージャを作成します。

別スキーマのテーブルをSELECT * で参照するビューを自動作成するストアドプロシージャ

スキーマとテーブルの作成

ストアドプロシージャを作成する前に、サンプルとして使用するスキーマとテーブルを作成しておきます。

今回はスキーマに「a」「b」「c」の3つを用意します。

CREATE SCHEMA a;
GO

CREATE SCHEMA b;
GO

CREATE SCHEMA c;
GO

CREATE SCHEMA a;

CREATE SCHEMA b;

CREATE SCHEMA c;

作成するスキーマのうち、「a」を基準となるスキーマにします。

他のスキーマ（bスキーマとcスキーマ）から参照するaスキーマのテーブルとして「TABLE1」「TABLE2」「TABLE3」を作成します。

CREATE TABLE a.TABLE1
(
    TABLE1_COLUMN1 int PRIMARY KEY,
    TABLE1_COLUMN2 nvarchar(20)
);
GO

CREATE TABLE a.TABLE2
(
    TABLE2_COLUMN1 int PRIMARY KEY,
    TABLE2_COLUMN2 datetime
);
GO

CREATE TABLE a.TABLE3
(
    TABLE3_COLUMN1 int PRIMARY KEY,
    TABLE3_COLUMN2 decimal(8, 2)
);
GO

CREATE TABLE a.TABLE1

(

TABLE1_COLUMN1 int PRIMARY KEY,

TABLE1_COLUMN2 nvarchar(20)

);

CREATE TABLE a.TABLE2

(

TABLE2_COLUMN1 int PRIMARY KEY,

TABLE2_COLUMN2 datetime

);

CREATE TABLE a.TABLE3

(

TABLE3_COLUMN1 int PRIMARY KEY,

TABLE3_COLUMN2 decimal(8, 2)

);

ストアドプロシージャが使用するテーブルの準備

今回作成する「別スキーマのテーブルをSELECT * で参照するビューを自動作成するストアドプロシージャ」では
①「テーブルを参照するビューの基準となるテーブルを保持するためのテーブル」
と
②「ビューを作成する先のスキーマを保持するためのテーブル」
を事前に作成しておいて、それを参照して処理を行います。

①「テーブルを参照するビューの基準となるテーブルを保持するためのテーブル」には、スキーマ名とテーブル名の列を用意します。

CREATE TABLE REFERENCE_TABLE
(
    SCHEMA_NAME nvarchar(50),
    TABLE_NAME nvarchar(100)
);

CREATE TABLE REFERENCE_TABLE

(

SCHEMA_NAME nvarchar(50),

TABLE_NAME nvarchar(100)

);

※ スキーマ名とテーブル名のサイズ（桁数）は、環境に合わせて変更してください。

このテーブルに参照元となるテーブルの情報を登録します。

REFERENCE_TABLEテーブルには、上記の3つのテーブルの情報を追加しておきます。

INSERT INTO REFERENCE_TABLE
    (SCHEMA_NAME, TABLE_NAME)
VALUES
    ('a', 'TABLE1'),
    ('a', 'TABLE2'),
    ('a', 'TABLE3');

INSERT INTO REFERENCE_TABLE

(SCHEMA_NAME, TABLE_NAME)

VALUES

('a', 'TABLE1'),

('a', 'TABLE2'),

('a', 'TABLE3');

②「ビューを作成する先のスキーマを保持するためのテーブル」には、スキーマ名の列を用意します。

CREATE TABLE SCHEMA_TO_CREATE_VIEW
(
    SCHEMA_NAME nvarchar(50)
);

CREATE TABLE SCHEMA_TO_CREATE_VIEW

(

SCHEMA_NAME nvarchar(50)

);

このテーブルにビューを作成するスキーマを登録します。

INSERT INTO SCHEMA_TO_CREATE_VIEW
    (SCHEMA_NAME)
VALUES
    ('b'),
    ('c');

INSERT INTO SCHEMA_TO_CREATE_VIEW

(SCHEMA_NAME)

VALUES

('b'),

('c');

上記のREFERENCE_TABLEテーブルとSCHEMA_TO_CREATE_VIEWテーブルへのデータのインサートは、1回の実行で複数のレコードを挿入する形式のSQLで記述しています。
1回のINSERTで複数行のレコードをテーブルに一括で挿入する方法の詳細については、以下の記事を参照してください。

ストアドプロシージャから参照するテーブルの準備ができたら、テーブルを参照するビューを作成するストアドプロシージャのSQLを記述していきます。
ストアドプロシージャでは、以下の処理を行います。

プロシージャ内で使用する変数を定義します。
変数には、「参照するテーブルのスキーマ名」「参照するテーブル名」「作成するビューのスキーマ名」を用意します。
テーブルカーソルの変数を用意し、SELECT文を記述します。
SELECT文ではREFERENCE_TABLEとビューを作成するスキーマを結合して、「参照するテーブルのスキーマ名」「参照するテーブル名」「作成するビューのスキーマ名」データを取得します。
テーブルカーソルを開きます。
フェッチでカーソルにレコードを読み込んでいきます。
SELECT文で取得する結果のすべてのレコードをループで処理します。
フェッチでは、「参照するテーブルのスキーマ名」「参照するテーブル名」「作成するビューのスキーマ名」の値をもとに、テーブルを「select *」で参照するビューを作成するSQL（CREATE VIEW文）を生成します。
ビューが存在する場合にはいったん削除（DROP VIEW）するSQLも生成します。
生成したSQLを実行します。
すべてのビューが作成できたらテーブルカーソルを閉じて破棄します。

上記の処理を行うストアドプロシージャのSQLを記載します。
プロシージャ名は「CREATE_TABLE_REFERENCE_VIEW」で作成します。

-- ストアドプロシージャが存在する場合は一旦削除する。
DROP PROCEDURE IF EXISTS CREATE_TABLE_REFERENCE_VIEW
GO

-- ストアドプロシージャを作成する。
CREATE PROCEDURE CREATE_TABLE_REFERENCE_VIEW

AS

-- テーブルカーソルで仕様する変数を定義する。
declare @TABLE_SCHEMA_NAME nvarchar(10)
declare @TABLE_NAME nvarchar(256)
declare @VIEW_SCHEMA_NAME nvarchar(10)

-- テーブルカーソルの定義とSQLの設定。
declare TABLE_CURSOR CURSOR FOR
select
    REFERENCE_TABLE.SCHEMA_NAME AS TABLE_SCHEMA_NAME,
    REFERENCE_TABLE.TABLE_NAME AS TABLE_NAME,
    SCHEMA_TO_CREATE_VIEW.SCHEMA_NAME AS VIEW_SCHEMA_NAME
from
    REFERENCE_TABLE
cross join
    SCHEMA_TO_CREATE_VIEW
where
    SCHEMA_TO_CREATE_VIEW.SCHEMA_NAME != REFERENCE_TABLE.SCHEMA_NAME;

-- テーブルカーソルを開く。
open TABLE_CURSOR;

-- フェッチしてカーソルを読み込んでいく
fetch next from TABLE_CURSOR
into @TABLE_SCHEMA_NAME, @TABLE_NAME, @VIEW_SCHEMA_NAME;

while @@fetch_status = 0
begin
    -- 作成するビューの名前。
    declare @VIEW nvarchar(270) = @VIEW_SCHEMA_NAME + '.' + @TABLE_NAME;
    -- 参照するテーブルの名前。
    declare @TABLE nvarchar(270) = @TABLE_SCHEMA_NAME + '.' + @TABLE_NAME;

    -- 念のためエラー処理を行っておく。
    if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@VIEW) AND type not in (N'V'))
    begin
        -- エラーメッセージを出力する。
        print '[×] ' + @VIEW + ' がビュー以外のオブジェクトとして存在しています。' + @TABLE + ' をもとにしたビュー ' + @VIEW + ' は作成されませんでした。'
    end
    else
    begin
        -- テーブル参照ビューを一旦削除するSQL。
        declare @DROP_VIEW_SQL nvarchar(max) = 'drop view if exists ' + @VIEW;
        -- テーブル参照ビューを作成するSQL。
        declare @CREATE_VIEW_SQL nvarchar(max) = 'create view ' + @VIEW + ' as select * from ' + @TABLE;

        -- SQLを実行してビューを作成する。
        exec dbo.sp_executesql @statement = @DROP_VIEW_SQL;
        exec dbo.sp_executesql @statement = @CREATE_VIEW_SQL;

        -- 作成完了メッセージを出力する。
        print '[○] ' + @TABLE + ' をもとにしたビュー ' + @VIEW + ' は作成されました。'
    end
    
    -- 次へ移動してカーソルを読み込む
    fetch next from TABLE_CURSOR
    into @TABLE_SCHEMA_NAME, @TABLE_NAME, @VIEW_SCHEMA_NAME;
end

-- テーブルカーソルを閉じる。
close TABLE_CURSOR;
-- テーブルカーソルを破棄する。
deallocate TABLE_CURSOR;

GO

-- ストアドプロシージャが存在する場合は一旦削除する。

DROP PROCEDURE IF EXISTS CREATE_TABLE_REFERENCE_VIEW

-- ストアドプロシージャを作成する。

CREATE PROCEDURE CREATE_TABLE_REFERENCE_VIEW

-- テーブルカーソルで仕様する変数を定義する。

declare @TABLE_SCHEMA_NAME nvarchar(10)

declare @TABLE_NAME nvarchar(256)

declare @VIEW_SCHEMA_NAME nvarchar(10)

-- テーブルカーソルの定義とSQLの設定。

declare TABLE_CURSOR CURSOR FOR

select

REFERENCE_TABLE.SCHEMA_NAME AS TABLE_SCHEMA_NAME,

REFERENCE_TABLE.TABLE_NAME AS TABLE_NAME,

SCHEMA_TO_CREATE_VIEW.SCHEMA_NAME AS VIEW_SCHEMA_NAME

from

REFERENCE_TABLE

cross join

SCHEMA_TO_CREATE_VIEW

where

SCHEMA_TO_CREATE_VIEW.SCHEMA_NAME != REFERENCE_TABLE.SCHEMA_NAME;

-- テーブルカーソルを開く。

open TABLE_CURSOR;

-- フェッチしてカーソルを読み込んでいく

fetch next from TABLE_CURSOR

into @TABLE_SCHEMA_NAME, @TABLE_NAME, @VIEW_SCHEMA_NAME;

while @@fetch_status = 0

begin

-- 作成するビューの名前。

declare @VIEW nvarchar(270) = @VIEW_SCHEMA_NAME + '.' + @TABLE_NAME;

-- 参照するテーブルの名前。

declare @TABLE nvarchar(270) = @TABLE_SCHEMA_NAME + '.' + @TABLE_NAME;

-- 念のためエラー処理を行っておく。

if exists (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@VIEW) AND type not in (N'V'))

begin

-- エラーメッセージを出力する。

print '[×] ' + @VIEW + ' がビュー以外のオブジェクトとして存在しています。' + @TABLE + ' をもとにしたビュー ' + @VIEW + ' は作成されませんでした。'

end

else

begin

-- テーブル参照ビューを一旦削除するSQL。

declare @DROP_VIEW_SQL nvarchar(max) = 'drop view if exists ' + @VIEW;

-- テーブル参照ビューを作成するSQL。

declare @CREATE_VIEW_SQL nvarchar(max) = 'create view ' + @VIEW + ' as select * from ' + @TABLE;

-- SQLを実行してビューを作成する。

exec dbo.sp_executesql @statement = @DROP_VIEW_SQL;

exec dbo.sp_executesql @statement = @CREATE_VIEW_SQL;

-- 作成完了メッセージを出力する。

print '[○] ' + @TABLE + ' をもとにしたビュー ' + @VIEW + ' は作成されました。'

end

-- 次へ移動してカーソルを読み込む

fetch next from TABLE_CURSOR

into @TABLE_SCHEMA_NAME, @TABLE_NAME, @VIEW_SCHEMA_NAME;

end

-- テーブルカーソルを閉じる。

close TABLE_CURSOR;

-- テーブルカーソルを破棄する。

deallocate TABLE_CURSOR;

作成したストアドプロシージャは、以下のように実行します。

EXEC CREATE_TABLE_REFERENCE_VIEW;

1	EXEC CREATE_TABLE_REFERENCE_VIEW;

ストアドプロシージャを実行すると、ビューが作成されることが確認できます。