Lest I forget… Wipe a database but retain schema


  1. EXEC sp_msforeachtable”ALTER TABLE ? NOCHECK CONSTRAINT all”
  2. EXEC sp_MSForEachTable”DELETE FROM ?”
  3. EXEC sp_msforeachtable”ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all”
  4. EXEC sp_MSforeachtable”DBCC CHECKIDENT ( ‘?’, RESEED, 0)”

The interesting things are:

  • There’s a clever ‘loop through all the tables’ stored proc
  • Line 1: You need to disable constraints (e.g. Foreign Keys etc) before you delete the data or they won’t let you delete stuff
  • Line 2: Then delete all the data from each table. You could probably use TRUNCATE which would be faster but wouldn’t give you the chance to not step 4 as it’ll reset the IDENTITYs automatically
  • Line 3: You need to turn the constraints back on again
  • Line 4: You can then reset all the IDENTITYs back to zero with the last statement

But worth also taking a look at the discussion on Stack Overflow about this approach and alternatives.

[I’m really just testing word publishing of posts - tbh, bit of a fail]

About these ads

Leave a Reply

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