Disable all constraints, clean all database

PROBLEM:

You need to disable all constraints, clean all database

SOLUTION:

-- Disable all constraints

EXEC sp_MSForEachTable @command1="print 'NOCHECK CONSTRAINT ?'", @command2="ALTER TABLE ? NOCHECK CONSTRAINT all"

When dealing with deleting data from tables which have foreign key relationships - which is basically the case with any properly designed database - we can disable all the constraints, delete all the data and then re-enable constraints

-- Delete data in all tables

EXEC sp_MSForEachTable @command1="print 'DELETE FROM ?'", @command2="DELETE FROM ?"

-- Enable all constraints

EXEC sp_MSForEachTable @command1="print 'CHECK CONSTRAINT ?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

-- If some of the tables have identity columns we may want to reseed them

EXEC sp_MSForEachTable @command1="print 'CHECKIDENT RESEED ?'", @command2="DBCC CHECKIDENT ( '?', RESEED, 0)"

Another option is to run the following code and save the results for both drop and recreate, then execute all the drops followed by your code and then recreate the constraints using the results from the second query

SELECT 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+' DROP CONSTRAINT ' + '[' + f.name + ']'

FROM .sys.foreign_keys AS f

INNER JOIN .sys.foreign_key_columns AS fc

ON f.OBJECT_ID = fc.constraint_object_id

SELECT 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']' +' ADD CONSTRAINT ' + '[' + f.name +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')'+'REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id, fc.referenced_column_id)+')' as Scripts

FROM .sys.foreign_keys AS f

INNER JOIN .sys.foreign_key_columns AS fc

ON f.OBJECT_ID = fc.constraint_object_id

To handle table constraints:

-- Disable all table constraints

ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

-- Enable all table constraints

ALTER TABLE MyTable CHECK CONSTRAINT ALL

To handle single constraint:

-- Disable single constraint

ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

-- Enable single constraint

ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint

If you find this solution useful, you are welcome to press one of the ads in this page.. Thanks!