Easy way to clear your SQL server database

Sometimes you want to make a clean start with your SQL server database since it’s filled up with old demo data. We all now that you can delete or truncate your tables easily by executing some T-SQL statements. When you only have a couple of tables and no complex foreign key dependencies you can do it by hand. When you database is larger and for more complex it can be a time consuming task and it’s different for each database schema.

The following script makes it really easy (beware: select the correct database!).

 1: ------------------------------------------------------------------------------------
 2: ---- NOTE: !!! DOUBLE CHECK SELECTED DATABASE !!!! ----
 3: ---- FIRST TRY RUNNING WITH TRANSACTIONS ON, LATER REPLACE ROLLBACK WITH COMMIT ----
 4: ------------------------------------------------------------------------------------
 5: begin transaction
 6:  
 7: -- disable all constraints
 8: EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
 9:  
 10: -- delete data in all tables
 11: EXEC sp_MSForEachTable "DELETE FROM ?"
 12:  
 13: -- enable all constraints
 14: exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"
 15:  
 16: -- some of the tables have identity columns we may want to reseed them
 17: EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"
 18:  
 19: rollback transaction
 20: --commit transaction

Note: I have the code surrounded by a transaction that performs a rollback by default. Just to make sure that you don´t run this script accidentally on the wrong database. You can download the script by using the link below

clear_all_database_tables.sql (1.54 kb)  

Hope this can save some time for you too,