Deleting All Your SQL Server Data
Oftentimes during the development cycle we have the need to clean up our databases. Recently, I’ve had the need to reset my SQL Server databases to a “no data” state to facilitate our development process.
Because my databases contain a lot of foreign-key relationships, a simple truncate on all the tables won’t work. I need to first, disable foreign key constraints, DELETE the data, and then re-enable the constraints. Then, for good measure, I reset the identity keys so we start auto-numbering the key fields at 1 instead of whatever number they were at. Here’s my script:
— disable the constraints
EXEC sp_MSforeachtable “ALTER TABLE ? NOCHECK CONSTRAINT all”
— delete the data
EXEC sp_MSForEachTable “DELETE FROM ?”
— re-enable the constraints
EXEC sp_MSforeachtable “ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”
–reseed the identity columns
EXEC sp_MSforeachtable “DBCC CHECKIDENT (‘?’, RESEED, 0)”
Admittedly, this tip goes into the category of “No Big Deal” for the SQL Experts out there, but as I’m only a sometime SQL Developer, it’s nice to have these little tricks lying around in case I need them again.