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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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