DB内のオブジェクト（テーブル）の全部の制約を無効/有効にする

#PASSJ Blogからの転記+修正版です。

SQL ServerのExport & Import作業やデータの削除時に、制約が邪魔をして、うまくお掃除できないことがあります。

そこで、一括して制約を無効/有効にするストアドを作ってみました。

※ただし、制約を無効化しても、制約が無くなるわけではありません。SQL Server 2005の場合、テーブル間の制約があると、制約が無効化状態であっても、TRUNCATEは出来ません。（ここは、Oracleと違うところみたいです）
TRUNCATEだと、とトランザクションが発生しないからでしょうか。

SQL Serverで、テーブルをTRUNCATEするためには、制約の削除 -> 再作成というステップを行わないとだめのようで、これはちょっと悲しい....。(他にもっといい方法があるのかもしれません。また、2008ではOKかもしれませんが、こちらは未確認）

ちなみに、DELETEとかINSERTは可能になります。（その代わり、SQLのトランザクションが発生するので、実行時間はそれなりにかかるかと思います）

---

とりあえず、全部のテーブルに対し、有効化/無効化を切り替えるストアドは、こんな感じとなりました。

CREATE PROCEDURE maintenance.sp_Disable_AllConstraints
@disable BIT = 1
AS
DECLARE
@sql VARCHAR(500),
@tableName VARCHAR(128),
@tableSchema VARCHAR(128)

-- List of all tables
DECLARE triggerCursor CURSOR
FOR
SELECT
t.TABLE_NAME AS TableName,
t.TABLE_SCHEMA AS TableSchema
FROM
INFORMATION_SCHEMA.TABLES t
ORDER BY
t.TABLE_NAME,
t.TABLE_SCHEMA

OPEN triggerCursor

FETCH NEXT FROM triggerCursor
INTO @tableName, @tableSchema

WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF @disable = 1
SET @sql = 'ALTER TABLE ' + @tableSchema
+ '.[' + @tableName + '] NOCHECK CONSTRAINT ALL'
ELSE
SET @sql = 'ALTER TABLE ' + @tableSchema
+ '.[' + @tableName + '] CHECK CONSTRAINT ALL'

PRINT 'Executing Statement - ' + @sql

EXECUTE ( @sql )
FETCH NEXT FROM triggerCursor
INTO @tableName, @tableSchema
END

CLOSE triggerCursor
DEALLOCATE triggerCursor

---

今回は、maintenanceというスキーマに作ってみました。
使い方は、こんな感じです。 ( -- 1で全制約を無効化、0で有効化 )

EXECUTE maintenance.sp_Disable_AllConstraints @disable = 1
--　結果
Executing Statement - ALTER TABLE xxxx.[Application] NOCHECK CONSTRAINT ALL
Executing Statement - ALTER TABLE xxxx.[ApplicationAttachementFile] NOCHECK
CONSTRAINT ALL
:
:

実は、上記のコードは、ほぼMSDNライブラリのコードを参考にしました。

外部キーの無効化って、Transact-SQLだとどうやるのかな...と思って検索したところ、NOCHECK CONSTRAINT で行えることが判明。しかも、テーブル単位で、ALL指定ができるんですね。

さらに、一括でトリガや外部制約を無効化するサンプルコードがMSDNライブラリに載っていたのですが、なぜか外部制約のためのスクリプトが「トリガの無効化」のコードになっていました(^^;

とりあえずSQLを良く見て、ちょこちょこと変えたら、希望通りの動作になったので、こちらに載せてみました。
ただ、実際は全部のスキーマに操作しちゃうのは良くないと思うので、スキーマ名を引数にとって実行するのが良いかなあ...と思っています。