Today I got one requirement from my collegue to delete all records from all Tables in the database. To do this we had to use either Delete OR Truncate command. If we use Delete command than 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 150 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 this 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”
The database was having more than 150 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 this 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”
Good
ReplyDelete