I had faced one requirement to clean entire database i.e. make EMPTY all tables from a database and the command to make empty table is either delete or truncate.
If we use delete command it maintains command history in LDF and if you use truncate it won’t so I have decided to use TRUNCATE.
The database was having more than 700 tables and each table was having constraints like primary key, foreign key because of which we could not truncate or delete records from tables.
To accomplish the task first we will have to remove all the constraints which was a time consuming and tedious job so I have searched for disabling the constraints and used below command (which comes with SQL Server 2008 R2 itself)
— disable all constraints
EXEC sp_msforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”
— TRUNCATE records from all tables
EXEC sp_MSForEachTable “TRUNCATE TABLE ?”
— enable all constraints
exec sp_msforeachtable “ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”