Skip to content

Instantly share code, notes, and snippets.

@andykuszyk

andykuszyk/Delete all data from a SQL Server database.md

Last active April 25, 2016 09:27
Show Gist options
  • Save andykuszyk/1dedafe3d014d987169cbe7f9c3a8cdf to your computer and use it in GitHub Desktop.
Save andykuszyk/1dedafe3d014d987169cbe7f9c3a8cdf to your computer and use it in GitHub Desktop.
Download ZIP
Delete all data from a SQL Server database
Raw
Delete all data from a SQL Server database.md

Delete all data from a SQL Server database

Why?

At the moment, I'm experimenting with interactions with a new database. I could very easily create the schema once and then take a backup of the database, restoring this backup every time I want to start again with a fresh copy.

However, it's a little more hassle free to leave the database in situ (especially because I want to use the same connection details each time) and just nuke all the data instead.

How?

Removing all the data from a database is almost as easy as running a DELETE FROM for every table, except that you need to suspend constraints temporarily whilst you're doing so:

EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"
EXEC sp_MSforeachtable "DELETE FROM ?"
EXEC sp_MSforeachtable "ALTER TABLE ? CHECK CONSTRAINT ALL"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment