Month: September 2009

SQL Server Stored Procedure to disable / enable all foreign keys and constraints in a database

Matt Simner SQL Server

I’ve been doing a bit of batch archiving work, and needed a nice and quick way to disable foreign keys and other constraints without actually removing them.  I found a nice article on disabling all constraints in a database, and thought I’d just take it one step further by making it into a Stored Procedure, and adding a parameter to toggle whether the constraints are enabled or disabled.

Nice and easy.  Here’s the script.

USE [YOURDBNAME]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_SetDBTableConstraints]’) AND type in (N’P’, N’PC’))
DROP PROCEDURE dbo.usp_SetDBTableConstraints
GO

CREATE PROCEDURE dbo.usp_SetDBTableConstraints(@Enable BIT)

AS

/*** DISABLE/ENABLE ALL TABLE CONSTRAINTS ************************

This script will disable all constraints on all tables within the database
that it is run in.

************************************************************/

SET NOCOUNT ON
SET ROWCOUNT 0 

DECLARE @Count INT
DECLARE @String NVARCHAR (1000)
DECLARE @ConstraintName VARCHAR(128)
DECLARE @TableName VARCHAR(128)

–Find all constraints and their respective tables from the sysobjects table and place into a temp table.
–Primary Key and Unique Constraints via Unique Indexes are not disabled through this command
–You should use the ALTER INDEX…DISABLE command in SQL Server 2005
SELECT 
        name                     AS constraintname,
        object_name(parent_obj)  AS tablename 
INTO #Const_Table
FROM sysobjects s 
where xtype in (‘F’)

SELECT @Count = Count(*) FROM #Const_Table

–Setting the rowcount to one allows for one row from the temp table to be picked off at a time.
–Used as an alternative to a cursor.
SET ROWCOUNT 1

–Loop until all rows in temp table have been processed.
WHILE @Count > 0
BEGIN
    –The rowcount of one ensures that only one tablename and constraint name is picked.
    SELECT @TableName = TableName, @ConstraintName = ConstraintName
    FROM #Const_Table

    –Build execution string to disable constraint.
    IF @Enable = 1 
        SET @String = ‘ALTER TABLE [‘+ @tablename + ‘] CHECK CONSTRAINT [‘ + @constraintname +‘]’
    ELSE
        SET @String = ‘ALTER TABLE [‘+ @tablename + ‘] NOCHECK CONSTRAINT [‘ + @constraintname +‘]’

    –Execute the SQL
    EXEC sp_executesql @string

    –Remove this row from the temp table, since it has now been processed.
    DELETE FROM #Const_Table WHERE ConstraintName = @ConstraintName and TableName = @TableName

    SET @Count = @Count – 1
END — Loop

SET ROWCOUNT 0

DROP TABLE #Const_Table
 

GO