SQL Server: Clean Database Records & reset Identity Columns

I had a problem while back to clean up my database and reset identity columns in all tables. But as we all know the chaos of the database, trace relations and delete records from child tables before parent tables because of foreign key constrains. By manually doing it could take so much time even the database is not that huge. Through some research on Google and books I was able to come up with a solution to achieve my target in few steps. The solution was to use built in stored procedure sp_MSforeachtable (which I already discussed in my previous blog post). So here is the following code bellow  for how I re-zeroed my Database:

/*Disable Constraints & Triggers*/
exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'

 /*Perform delete operation on all table for cleanup*/
exec sp_MSforeachtable 'DELETE ?'

/*Enable Constraints & Triggers again*/
exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'

/*Reset Identity on tables with identity column*/
exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END' 

/Adnan



This post is from my old blog posted on February 2011, that unfortunately no longer exist.