Roman's Weekly SQL Server Tip - How to quickly delete all data in the database
This tip comes from my latest project. If you run into a scenario where you need to delete all of the data in your database, you can do it easily with just two lines of code using the MSForEachTable stored procedure. The trick here is to first disable referential integrity checks so you can delete data from parent tables. If you have a lot of data, you may want to rewrite the delete part and turn it into a batch delete, in my experience 100,000 rows is a reasonable chunk to delete in one shot. Here is the code:
-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO