Generate Commands to Enable/Disable FKs
Applicability:
SQL Server 2000: Tested
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Credits:
Author: ChillyDBA
Date: 4 Jul 2012
Description
SP to generates commands to enable and disable all FKs in a database.
Only tables with FK constraints have commands generated.
No changes to tables are made by this SP.
Code
Stored Procedure:
DROP PROCEDURE dbo.usp_GenerateEnableDisableFKCommands
GO
CREATE PROCEDURE dbo.usp_GenerateEnableDisableFKCommands
AS
/***************************************************************
Purpose: To generate commands to enable or disable all FKs
No changes are made by this stored procedure
Author: ChillyDBA
History: 4 Jul 2012 - Initial Issue
****************************************************************/
-- generate code to disable all FKs (only for tables that have them)
SELECT
'ALTER TABLE '
+ SCHEMA_NAME(so2.schema_id)
+ '.'
+ so2.name
+ ' WITH NOCHECK CHECK CONSTRAINT ALL' AS 'DISABLE FK Commands'
FROM sys.objects so1
INNER JOIN sys.objects so2
ON so1.parent_object_id = so2.OBJECT_ID
WHERE so1.TYPE = 'F'
ORDER BY so2.name
-- generate code to enable all FKs (only for tables that have them)
SELECT
'ALTER TABLE '
+ SCHEMA_NAME(so2.schema_id)
+ '.'
+ so2.name
+ ' ENABLE CHECK CHECK CONSTRAINT ALL' AS 'ENABLE FK Commands'
FROM sys.objects so1
INNER JOIN sys.objects so2
ON so1.parent_object_id = so2.OBJECT_ID
WHERE so1.TYPE = 'F'
ORDER BY so2.name
Test Code:
USE AdventureWorksDW
GO
EXEC dbo.usp_GenerateEnableDisableFKCommands