Delete all data in SQL Server database

sql sql-server delete-row multi-table-delete
AndreyAkinshin picture AndreyAkinshin · Sep 10, 2010 · Viewed 207.8k times · Source

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?

Answer

Ryan Kirkman picture Ryan Kirkman · Aug 2, 2012

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

Related questions

Is it possible to delete from multiple tables in the same SQL statement?

It's possible to delete using join statements to qualify the set to be deleted, such as the following: DELETE J FROM Users U inner join LinkingTable J on U.id = J.U_id inner join Groups G on J.G_…

Read More
Delete records within Instead Of Delete trigger

I want to have an instead of delete trigger so that I can get text field values out of the table row that's being deleted to preserve those fields when the actual delete occurs. For some reason I can't pull …

Read More
Deleting rows in a table a chunk at a time

I have a single-column table Ids, which whose column ID is of type uniqueidentifier. I have another table MyTable which has an ID column as well as many other columns. I would like to delete rows from MyTable 1000 at a …

Read More