Roman's Weekly SQL Server Tip - How to quickly delete all data in the database, version 2 ![Rated Excellent [5 out of 5].](/WebLog/Themes/default/images/Star5.gif)
My tip from last week about deleting all data in the database easily seems to have generated a lot of interest and a good discussion about using DELETE vs. TRUNCATE TABLE. Obviously the truncate command is more efficient but you get an error if you try to run it on a table that's a parent to another table. So I started thinking that maybe there is an easy way to figure out if a table is a parent. In the end, my intuition about the SERVERPROPERTY proved correct - if you pass in 'TableHasForeignRef' as a parameter, SERVERPROPERTY returns 1 if the table has any foreign key references.
So here is is the new version of the script, it uses TRUNCATE TABLE on stand-alone and child tables, or DELETE otherwise:
-- 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