テーブルなどのデータベースオブジェクトの存在確認 [SQL Server]

SQLServerでテーブルやビューなどのデータベースのオブジェクトを作成するSQLを実行する時、作成するオブジェクトが既に存在しているとエラーになり、以下のメッセージが出力されます。

メッセージ 2714、レベル 16、状態 6、行 2
データベースに ‘オブジェクト名’ という名前のオブジェクトが既に存在します。

オブジェクト名の部分は作成しようとしたオブジェクトの名前になります。

既にオブジェクトがある場合にSQLServerがエラーを出してくれるので、誤ってオブジェクトが上書きされるのを防ぐことができます。
これは、非常にありがたい仕様です。

しかし、場合によってはエラーを出力したくない時もあります。

そこで今回は、オブジェクトが存在するかどうかを確認する方法を紹介します。

オブジェクトの有無を確認することができれば、オブジェクトが存在しない場合のみ作成するSQLを実行したり、オブジェクトが存在する場合はSQLを実行しないようにしたりすることができます。

使用するテーブル

本記事では、例題のSQLの記述で、以下のテーブルを使用します。

テーブル定義

STAFFテーブル
列名 データ型 PK
ID int 1
NAME nvarchar(50)
GENDER int
GENDERテーブル
列名 データ型 PK
ID int 1
NAME nvarchar(1)

テーブルのCREATE文

PERSONテーブル

GENDERテーブル

テーブルのINSERT文

PERSONテーブル

GENDERテーブル

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

1回のINSERT(インサートSQL)で複数行のレコードを一括挿入(追加)する
SQLServerやMySQLなどのデータベースで、テーブルにレコードをINSERT文使用して追加するには、通常は以下のように記述します。 ...

オブジェクトが存在しているかどうかを検証するSQL

IF EXISTS

オブジェクトの有無を確認するためには、以下のような構文を使います。

オブジェクトの情報を取得するSQLの部分にオブジェクトの有無を判断するための条件となるSQLを指定します。

例えば、PERSONテーブルが存在するかどうかを判定するための条件は、次のようになります。

システムビューの「sys.objects」に対してオブジェクトのID(object_id)とタイプ(type)を指定してレコードを取得しています。
タイプに指定している「N’U’」は、ユーザー定義のテーブルを表します。

上記の条件式をIF EXISTS文に指定すると、次のようになることが分かると思います。

IF EXISTS文の後にSQLを記述することで、条件を満たしている場合のみSQLを実行することができます。

PERSONテーブルが存在する場合のみSELECT文を実行する場合は、以下のようになります。

IF EXISTS文でオブジェクトの有無を判定した後に実行する命令文(SQL)が複数ある場合は、BEGINとENDで囲ってください。

BEGIN~END構文を使用せずに、以下のように記述してしまうと

1行目の命令文の「INSERT INTO [dbo].[PERSON] VALUES (6, ‘古川桜’, 2);」は、IF EXISTSの結果に従って実行されますが、2行目の命令文の「INSERT INTO [dbo].[PERSON] VALUES (7, ‘三浦純一’, 1);」は、IF EXISTSの条件には関係なく実行されてしまいます。

SQLServer(Transact-SQL)では、IF文を使った条件分岐では、1つの命令文しか条件式として扱ってくれません。
ですので、IF文を使用して条件を判定した後に複数の命令文を実行したい場合は、BEGIN~END構文を利用して条件に一致した際に実行される命令文をすべて囲ってください。

IF NOT EXISTS

IF EXISTS文では、条件を満たす場合だけではなく、条件を満たさない場合の処理を行うこともできます。
条件を満たさない場合にSQLを実行する場合は、「IF EXISTS」の部分を「IF NOT EXISTS」にします。

IF EXISTS ~ ELSE ~

IF EXISTS文はIF構文なのでELSEを使うこともできます。

テーブル存在確認 補足

オブジェクトが存在しているかどうかを確認する際にsys.objectsを参照していますが、オブジェクトがテーブルであればsys.tablesを利用することもできます。

オブジェクトの再作成

オブジェクトが存在する状態で上書きをしたい場合は、作成するオブジェクトを一旦削除してから再度作成することになります。
オブジェクトを削除する際に、IF EXISTS文を利用することで、オブジェクトが存在する場合のみDROP命令文を実行することが可能になります。

DROP時のオブジェクトの存在確認は、簡略化して次のように1行で記述することもできます。

テーブル以外のオブジェクト

ここまで記載した内容は、すべてテーブルに関するものでしたが、テーブル以外のオブジェクトであってもIF EXISTS文の使い方は同様です。

例えば以下のようなビューがあるとします。

PEOPLEビュー

ビューを作成するCREATE SQL

このビューが存在するかどうかを検証する場合は

のような条件式を記述することになります。
タイプに指定している「N’V’」は、ユーザー定義のビューを表します。

ビュー存在確認 補足

オブジェクトがビューであればsys.objectsの代わりにsys.viewsを利用することもできます。

まとめ

今回ご紹介したオブジェクトの存在有無を確認する

  • IF EXISTS
  • IF NOT EXISTS
  • IF EXISTS ~ ELSE

の構文は、テーブルやビューなどのオブジェクトを作成する際によく使います。
オブジェクトを削除する前には「IF EXISTS」、オブジェクトを作成する前には「IF NOT EXISTS」を使う癖をつけておくと、誤ってSQL命令が実行されることを防ぐことができます。