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