SQLServerでテーブルやビューなどのデータベースのオブジェクトを作成するSQLを実行する時、作成するオブジェクトが既に存在しているとエラーになり、以下のメッセージが出力されます。
データベースに ‘オブジェクト名’ という名前のオブジェクトが既に存在します。
※ オブジェクト名の部分は作成しようとしたオブジェクトの名前になります。
既にオブジェクトがある場合にSQLServerがエラーを出してくれるので、誤ってオブジェクトが上書きされるのを防ぐことができます。
これは、非常にありがたい仕様です。
しかし、場合によってはエラーを出力したくない時もあります。
そこで今回は、オブジェクトが存在するかどうかを確認する方法を紹介します。
オブジェクトの有無を確認することができれば、オブジェクトが存在しない場合のみ作成するSQLを実行したり、オブジェクトが存在する場合はSQLを実行しないようにしたりすることができます。
目次
使用するテーブル
本記事では、例題のSQLの記述で、以下のテーブルを使用します。
テーブル定義
| 列名 | データ型 | PK |
|---|---|---|
| ID | int | 1 |
| NAME | nvarchar(50) | |
| GENDER | int |
| 列名 | データ型 | PK |
|---|---|---|
| ID | int | 1 |
| NAME | nvarchar(1) |
テーブルのCREATE文
|
1 2 3 4 5 |
CREATE TABLE [dbo].[PERSON]( [ID] [int] NOT NULL PRIMARY KEY, [NAME] [nvarchar](50) NULL, [GENDER] [int] NULL ); |
|
1 2 3 4 |
CREATE TABLE [dbo].[GENDER]( [ID] [int] NOT NULL PRIMARY KEY, [NAME] [nvarchar](1) NULL ); |
テーブルのINSERT文
|
1 2 3 4 5 6 7 8 |
INSERT INTO [dbo].[PERSON] (ID, NAME, GENDER) VALUES (1, '山田太郎', 1), (2, '佐藤花子', 2), (3, '田中順子', 2), (4, '鈴木一郎', 1), (5, '二階堂 宗次郎', 1); |
|
1 2 3 4 5 |
INSERT INTO [dbo].[GENDER] (ID, NAME) VALUES (1, '男'), (2, '女'); |
上記のPERSONテーブルとGENDERテーブルへのデータのインサートは、複数のレコードを1回のSQLで一括挿入する形式で記述しています。
1回のINSERTで複数行のレコードをテーブルに一括で挿入する方法については、以下の記事を参照してください。
オブジェクトが存在しているかどうかを検証するSQL
IF EXISTS
オブジェクトの有無を確認するためには、以下のような構文を使います。
|
1 |
IF EXISTS ( オブジェクトの情報を取得するSQL ) |
オブジェクトの情報を取得するSQLの部分にオブジェクトの有無を判断するための条件となるSQLを指定します。
例えば、PERSONテーブルが存在するかどうかを判定するための条件は、次のようになります。
|
1 |
SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PERSON]') AND type in (N'U') |
システムビューの「sys.objects」に対してオブジェクトのID（object_id）とタイプ（type）を指定してレコードを取得しています。
タイプに指定している「N’U’」は、ユーザー定義のテーブルを表します。
上記の条件式をIF EXISTS文に指定すると、次のようになることが分かると思います。
|
1 |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PERSON]') AND type in (N'U')) |
IF EXISTS文の後にSQLを記述することで、条件を満たしている場合のみSQLを実行することができます。
|
1 2 |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PERSON]') AND type in (N'U')) -- 存在する場合に実行する命令文（処理）をここに記述する |
PERSONテーブルが存在する場合のみSELECT文を実行する場合は、以下のようになります。
|
1 2 3 |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PERSON]') AND type in (N'U')) -- SELECT文で性別とIDの昇順にレコードを抽出する SELECT * FROM [dbo].[PERSON] ORDER BY GENDER, ID; |
IF EXISTS文でオブジェクトの有無を判定した後に実行する命令文（SQL）が複数ある場合は、BEGINとENDで囲ってください。
|
1 2 3 4 5 6 7 |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PERSON]') AND type in (N'U')) -- ↓ BEGIN INSERT INTO [dbo].[PERSON] VALUES (6, '古川桜', 2); INSERT INTO [dbo].[PERSON] VALUES (7, '三浦純一', 1); END -- ↑ |
BEGIN～END構文を使用せずに、以下のように記述してしまうと
|
1 2 3 |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PERSON]') AND type in (N'U')) INSERT INTO [dbo].[PERSON] VALUES (6, '古川桜', 2); INSERT INTO [dbo].[PERSON] VALUES (7, '三浦純一', 1); |
1行目の命令文の「INSERT INTO [dbo].[PERSON] VALUES (6, ‘古川桜’, 2);」は、IF EXISTSの結果に従って実行されますが、2行目の命令文の「INSERT INTO [dbo].[PERSON] VALUES (7, ‘三浦純一’, 1);」は、IF EXISTSの条件には関係なく実行されてしまいます。
ですので、IF文を使用して条件を判定した後に複数の命令文を実行したい場合は、BEGIN～END構文を利用して条件に一致した際に実行される命令文をすべて囲ってください。
IF NOT EXISTS
IF EXISTS文では、条件を満たす場合だけではなく、条件を満たさない場合の処理を行うこともできます。
条件を満たさない場合にSQLを実行する場合は、「IF EXISTS」の部分を「IF NOT EXISTS」にします。
|
1 2 |
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PERSON]') AND type in (N'U')) -- 存在しない場合に実行する命令文（処理）をここに記述する |
IF EXISTS ～ ELSE ～
IF EXISTS文はIF構文なのでELSEを使うこともできます。
|
1 2 3 4 5 6 |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GENDER]') AND type in (N'U')) -- オブジェクトが存在する場合 PRINT('The GENDER table exists.') ELSE -- オブジェクトが存在しない場合 PRINT('The GENDER table does not exist.') |
|
1 2 3 4 5 6 |
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GENDER]') AND type in (N'U')) -- オブジェクトが存在しない場合 PRINT('The GENDER table does not exist.') ELSE -- オブジェクトが存在する場合 PRINT('The GENDER table exists.') |
テーブル存在確認 補足
オブジェクトが存在しているかどうかを確認する際にsys.objectsを参照していますが、オブジェクトがテーブルであればsys.tablesを利用することもできます。
|
1 |
IF EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[dbo].[PERSON]')) |
オブジェクトの再作成
オブジェクトが存在する状態で上書きをしたい場合は、作成するオブジェクトを一旦削除してから再度作成することになります。
オブジェクトを削除する際に、IF EXISTS文を利用することで、オブジェクトが存在する場合のみDROP命令文を実行することが可能になります。
|
1 2 |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PERSON]') AND type in (N'U')) DROP TABLE [dbo].[PERSON]; |
DROP時のオブジェクトの存在確認は、簡略化して次のように1行で記述することもできます。
|
1 |
DROP TABLE IF EXISTS [dbo].[PERSON]; |
テーブル以外のオブジェクト
ここまで記載した内容は、すべてテーブルに関するものでしたが、テーブル以外のオブジェクトであってもIF EXISTS文の使い方は同様です。
例えば以下のようなビューがあるとします。
PEOPLEビュー
ビューを作成するCREATE SQL
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
CREATE VIEW [dbo].[PEOPLE] AS SELECT PERSON.ID AS PERSON_ID, PERSON.NAME AS PERSON_NAME, PERSON.GENDER AS GENDER_ID, GENDER.NAME AS GENDER_NAME FROM [dbo].[PERSON] LEFT OUTER JOIN [dbo].[GENDER] ON PERSON.GENDER = GENDER.ID; |
このビューが存在するかどうかを検証する場合は
|
1 |
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[PEOPLE]') AND type in (N'V')) |
のような条件式を記述することになります。
タイプに指定している「N’V’」は、ユーザー定義のビューを表します。
ビュー存在確認 補足
オブジェクトがビューであればsys.objectsの代わりにsys.viewsを利用することもできます。
|
1 |
IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[PEOPLE]')) |
まとめ
今回ご紹介したオブジェクトの存在有無を確認する
- IF EXISTS
- IF NOT EXISTS
- IF EXISTS ～ ELSE
の構文は、テーブルやビューなどのオブジェクトを作成する際によく使います。
オブジェクトを削除する前には「IF EXISTS」、オブジェクトを作成する前には「IF NOT EXISTS」を使う癖をつけておくと、誤ってSQL命令が実行されることを防ぐことができます。
