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,