SQL Server: delete all rows of all tables to empty a database

In SQL Server it's possible to empty a database deleting all rows of all tables with the following simple script I've found in the blog post T-SQL Trick for Deleting All Data in Your Database:

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable ' IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1 DELETE FROM ? else TRUNCATE TABLE ? '
GO

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

But if the database contains identity columns and you want to reset them, you can visit the forum topic Truncate All Tables, answer of 06/13/2007 at 11:38:52, or download directly the code from the file EmptyAllTables.sql.txt.

Add comment

Loading