I found this awesome script to easily delete all data in all the tables of a database. There might be an issue with large tables, or if some constraints were previously disabled then you will need to disable them again afterwards. Here's the T-SQL, I found it here
-- 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
Subscribe to:
Post Comments (Atom)
5 comments:
That looks SQLServer'ish :) Or what DB are you working in?
That looks SQLServer'ish :) Or what DB are you working in?
MS Server 2000/2005
Personally, once you got all the referential constraints taken care of, I'd just do a load replace from /dev/null ... I think that would be faster...or is TRUNCATE TABLE essentially the same thing in MSese? Do you guys know what it does under the covers?
TRUNCATE TABLE = Delete without logging...
Post a Comment