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