Throughout our development life (more so on green field project).
We often require to clear out all the test data in the database, and have it re-populated with new clean data, or just have it ready for deployment.
Well I've came across this script to clean out all the data in my database.
Simply copy and paste the below lines into your query editor.
Of course you should always make backup before executing a 'deletion' script. Right?!
/*
This batch t-sql deletes data from all the data in the database.
Here is what it does:
1) Disable all the constraints/triggers for all the tables
2) Delete the data for each child table & stand-alone table
3) Reseed the identities of all tables to its initial value.
4) Enable all the constraints/triggers for all the tables.
Note: This is a batch t-sql code which does not create any object in database.
If any error occurs, re-run the code again. It does not use TRUNCATE statement to delete
the data and instead it uses DELETE statement. Using DELETE statement can increase the
size of the log file and hence used the CHECKPOINT statement to clear the log file after
every DELETE statement.
Usage: replace #database_name# with the database name (that you wanted to truncate) and just execute the script in query analyzer.
*/
--use [#database_name#]
Set NoCount ON
/*
Step 1: Disable all constraints
*/
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
/*
Step 2: Delete the data for all child tables & those which has no relations
*/
EXEC sp_MSForEachTable 'DELETE FROM ?'
/*
Step 3: Reset all identity columns
*/
EXEC sp_MSForEachTable 'DBCC CHECKIDENT (''?'', RESEED, 1)'
/*
Step 4: Enable all constraints
*/
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
/*
Double check if all data are cleared
*/
EXEC sp_MSFOREACHTABLE 'SELECT * FROM ?'
