Generate Commands to Enable/Disable FKs

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