参照整合性制約の一括無効化


たまにやるのでメモ

SELECT 
 obj.name TAB_NAME
 ,fk.name NAME
FROM
 sys.foreign_keys fk
LEFT OUTER JOIN
 sys.objects obj
ON
 obj.object_id = fk.parent_object_id
WHERE
 is_disabled = 0
;
GO

DECLARE @tabName AS VARCHAR(255);
DECLARE @fkName AS VARCHAR(255);

DECLARE tmpCur cursor FOR
SELECT 
 obj.name TAB_NAME
 ,fk.name NAME
FROM
 sys.foreign_keys fk
LEFT OUTER JOIN
 sys.objects obj
ON
 obj.object_id = fk.parent_object_id
WHERE
 is_disabled = 0;-- is_disabled

OPEN tmpCur;
BEGIN TRY

FETCH NEXT FROM tmpCur INTO @tabName,@fkName;
WHILE (@@fetch_status = 0)
BEGIN
	--外部キー制約を無効にする
	--ALTER TABLE てーぶるめい NOCHECK CONSTRAINT せいやくめい
	--外部キー制約を有効にする
	--ALTER TABLE てーぶるめい CHECK CONSTRAINT せいやくめい
 DECLARE @execQ VARCHAR(MAX)= 'ALTER TABLE ' + @tabName + ' NOCHECK CONSTRAINT' + @fkName;
 exec @execQ;

 FETCH NEXT FROM tmpCur INTO @tabName,@fkName; 
END


END TRY
BEGIN CATCH
END CATCH


CLOSE tmpCur;
DEALLOCATE tmpCur;
GO
カテゴリー: 運用, 開発 タグ: パーマリンク