SQL 2008 : Delete all tables in a db

Published by exdone

Posted on November 27, 2009

Reference

EXEC sp_MSforeachtable @command1 = “DELETE FROM ?”

EXEC sp_MSforeachtable @command1 = “TRUNCATE TABLE ?”

——
You won’t be able to run TRUNCATE against all tables if you have foreign keys references

Here is one way to circumvent that

— First 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

— Now enable referential integrity again
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO

Categories: SQL