Wednesday 28 May 2008

How to disable all foreign key constraints on all tables in SQL Server?

The other day I wanted to import some table data from Excel using the import data facility. But, as it goes, SQL Server insisted to import some tables having FKs before their corresponding PK tables. So we have to disable/enable FK constraints on some or all tables.
Here is a very usefull script from decipherinfosys
I tried this out on SQL server 2000. Its working fine.

/************** DISABLE 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.
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


After having re-enabled the constrains you can check constraints using the following command:

DBCC CHECKCONSTRAINTS

And look at your constraints using this command:

SELECT (CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

No comments: