/************************** DISABLE ALL TABLE CONSTRAINTS **********************************
This script will enable or 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)
DECLARE @SchemaName 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
	o.name AS ConstraintName,
	sc.name AS SchemaName,
	object_name(o.parent_obj) AS TableName
INTO #Const_Table
FROM sysobjects o, sys.schemas sc
WHERE xtype IN ('F') AND o.uid = sc.schema_id
 
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, @SchemaName = SchemaName, @ConstraintName = ConstraintName
	FROM #Const_Table
 
	--Comment or uncomment the following statements according to your needs
	--Build execution string to enable constraint.
	SET @String = 'ALTER TABLE ['+ @SchemaName + '].['+ @TableName + '] WITH CHECK CHECK CONSTRAINT [' + @ConstraintName +']'
	--Build execution string to disable constraint.
	--SET @String = 'ALTER TABLE ['+ @SchemaName + '].['+ @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 AND SchemaName = @SchemaName
 
	SET @COUNT = @COUNT - 1
END -- Loop
 
DROP TABLE #Const_Table
 
SET ROWCOUNT 0