Declarative Referential Integrity - 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

ċ
usp_GenerateEnableDisableFKCommands.sql
(1k)
Andy Hughes,
Jul 4, 2012, 10:18 AM
Comments