Nocheck FK constraint to Load /Clean Data

-- disable all constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- delete data in all tables
EXEC sp_MSForEachTable "DELETE FROM ?"

-- enable all constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

Sometimes to populate an empty database from external data source or
debug a problem in the database I need to disable ALL triggers and
constraints.

To do so I use the following code:

sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER  all"

To enable all constraints and triggers:

exec sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
sp_msforeachtable @command1="print '?'", @command2="ALTER TABLE ? ENABLE TRIGGER  all"


DATA CLEAN:

 -- delete data
 if some of the tables have identity columns we may want to reseed them
 EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"


 --truncate data
 identity columns will be atuto to reseed to 0


ISSUE RESOLUTION:

1. cannot 'truncate' table A even diable the constrains

Reason:

(1) table A is a parent table, has been referenced by other tables
(2) (MSDN : http://msdn.microsoft.com/en-us/library/aa260621(SQL.80).aspx)
 "You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY
constraint; instead, use DELETE statement without a WHERE clause.
Because TRUNCATE TABLE is not logged, it cannot activate a trigger."

Solutions :

(1) use 'DELETE' without 'where-clause'

e.g :  DELETE FROM A

(2)  Drop constraint

   1. Drop the constraints
   2. Trunc the table
   3. Recreate the constraints.