sqlsql-serverdelete-rowmulti-table-delete

Delete all data in SQL Server database

How I can delete all records from all tables of my database? Can I do it with one SQL command or I need for one SQL command per one table?

Solution

  • SQLMenace's solution worked for me with a slight tweak to how data is deleted - DELETE FROM instead of TRUNCATE.

    -- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 
GO 

EXEC sp_MSForEachTable 'DELETE FROM ?' 
GO 

-- enable referential integrity again 
EXEC sp_MSForEachTable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' 
GO