I found a very handy SQL script on this post, that allows you to enable or disable all constraints on a database. The only problem I had was that my database uses schemas to organize the tables, so the script failed to work.
I made a modification on the script to solve the problem, so here it is:
/************************** 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 |