SQL Server: Cleaning all Database Tables and Reset Identity Columns by forcing Truncate or Delete

We all know that during the development process of any software or web application connected to an SQL Server database, all the tables contain what we call ‘trash’ data which is non useful data entered by developers for testing their code. This data should be removed before the delivery of the application to the client, where the database should be clean and all the tables indexes regarding the Identity column must be reset to zero index.

I’ve faced such an issue several times. The data removal using the usual Delete or Truncate statements isn’t a hard task when the number of tables in the database and the relations between them are not that large. But when it comes to a database that contains a large bunch of tables and complicated relationships among them all, then this represents a serious problem.

Well, the solution is by iterating over all tables in the database, disable the constraints and then delete the data found in each table. Don’t panic, we’re not going to write any script to accomplish this mission and of course we’ll not do this task manually by going to each table and disable the constraints and after that deleting the records. Thanks for the built in stored procedure sp_MSforeachtable which allows you to loop over all the tables in the desired database and execute any command you supply within the parameters.

The solution will be as follows:

1. Disable Constraints and Triggers by executing the following command:
   exec sp_MSforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? DISABLE TRIGGER ALL'
2. Now delete all the records found in all tables in your database by forcing cleanup through Delete or Truncate
   exec sp_MSforeachtable 'DELETE ?'
3. We have to enable the Constraints and Triggers back again:
   exec sp_MSforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
exec sp_MSforeachtable 'ALTER TABLE ? ENABLE TRIGGER ALL'
4. The final step is to reset the Identity column in all tables back to zero base index:
   exec sp_MSforeachtable 'IF OBJECTPROPERTY(OBJECT_ID(''?''), ''TableHasIdentity'') = 1 BEGIN DBCC CHECKIDENT (''?'',RESEED,0) END'

Now all the tables in the database are clean and their identity columns are reset to zero.