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.

Advertisements

About Ed Jones

Ed is a .NET and Integration architect for Avanade in the Twin Cities. Contact Ed

What do you think?

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: