Tuesday, April 24, 2012

Purge all data in database

Recently I have been tasked to purge all data in the database. When trying to delete data from tables which have foreign key relationship the standard purge routine will fail or you have to spend time to get which table to purge first. Also there is an issue of identity as when you delete data from the table the identity will not go back to starting point. After few research, I have got the best method which is shown below. 1. Disable all constraints in the database using the following command exec sp_msforeachtable "ALTER TABLE ? nocheck contraint all" 2. Delete all data in the database exec sp_MSForEachTable "DELETE FROM ?" 3. Enable all constraints exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" 4. Reset tables with identity. Note when you run the following it will give you error if the table doesn't have identity column. Ignore the errors. exec sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

No comments: