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
DROP PROCEDURE dbo.usp_SetDBTableConstraints
GO
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
IF @Enable = 1
SET @String = ‘ALTER TABLE [‘+ @tablename + ‘] CHECK CONSTRAINT [‘ + @constraintname +‘]’
ELSE
SET @String = ‘ALTER TABLE [‘+ @tablename + ‘] NOCHECK CONSTRAINT [‘ + @constraintname +‘]’
EXEC sp_executesql @string
DELETE FROM #Const_Table WHERE ConstraintName = @ConstraintName and TableName = @TableName
END — Loop SET ROWCOUNT 0 DROP TABLE #Const_Table
GO
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 ONSET 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