MS SQL Server: Quick T-SQL to Delete All User Table Data

Here’s a quick (hack) way to delete all user table data from a Microsoft SQL Server database using the undocumented procedure sp_MSforeachtable. As the name hints, this procedure repeats a SQL statement against all user tables within a database.

This example assumes all identity columns are seeded at 1. You may not be able to do that in your environment.

Also note, you could use TRUNCATE TABLE, but only if you no foreign key constraints on your tables.

[cc lang=sql”]

EXEC sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSforeachtable ‘DELETE ?’
EXEC sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
EXEC sp_MSforeachtable ‘IF (OBJECTPROPERTY(object_id(”?”), ”TableHasIdentity”) = 1) DBCC CHECKIDENT(”?”,RESEED,1)’

Hope this helps!

This entry was posted in MS SQL Server, T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *