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



— TRUNCATE records from all tables



— enable all constraints

exec sp_msforeachtable “ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”




Prashant Deshpande





Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s