Monthly Archives: October 2011

Delete or truncate all tables data within a database SQL Server 2008 R2

Dear All,

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”

 

Thanks

 

Prashant Deshpande

 

 

 

Advertisements

Leave a comment

Filed under Uncategorized