Delete Data in All Tables

By Michael Detras

To delete all the rows in a SQL Server table, we just use the following SQL script: DELETE FROM TABLE_NAME. To delete the data in all tables, we can use the spMSForEachTable stored procedure to execute the script for each table.

To delete all rows in all tables, just execute the following script:

EXEC sp_MSForEachTable 'DELETE FROM ?'
GO

If there are constraints such as foreign key references, then the deletion of all data might fail. Here is the updated script that removes the constraints temporarily.

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'DELETE FROM ?'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Delete Data in All Tables  (801 Views)