T-SQL Trick for Deleting All Data in Your Database
I came across a great tip in Roman Rehak's blog (http://www.sqljunkies.com/WebLog/roman/archive/2006/03/08/18620.aspx) on how to use simple t-sql to delete all the data in all the tables of a database. I've added this to my SQL toolbox for two reasons:
(1) It's a quick and easy way to clear out bogus/bad data that I may have entered while building a new application. This is especially helpful if you use referential integrity and use parent/child table dependencies.
(2) I tend to due a lot of ad hoc security evangelism while working with customers. One of the items I often highlight is that too many folks have Enterprise Manager (SQL 2000) or Management Studio (SQL 2005) on their desktop and they have too much power in respective databases. The response I often receive is “That's not a big deal. What damage can someone possibly do?” Well, give me 5 seconds on someone's machine with this t-sql and (poof!) your production data can be gone! Granted, I would never do it but this t-sql clearly shows that it can be done quickly.
Here's the t-sql... Thanks Roman!
-- 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