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

Posted in SQL
Comments Off Posted by admin