Deleting All Data From User Tables

As most SQL Databases that have been correctly designed will have Foreign Key Check Constraints, this is not as easy as it might at first appear.


You might think that you could use the following:

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

Unfortunately any Foreign Key Check Constraints on any tables will rightly throw up an error if you try this.

Instead you will need to disable the Check Constraints first, then re-enable them. Again unfortunately you
will then need to use the DELETE FROM Statement instead of the TRUNCATE TABLE statement, because the
TRUNCATE TABLE Statement will not work unless you drop all your Foreign Key Constraints and then re-create them.

The following Transact SQL Statement will work:

-- disable all constraints
EXEC sp_MSForEachTable 'ALTER TABLE ?
NOCHECK CONSTRAINT all'

-- delete data in all tables
EXEC sp_MSForEachTable 'DELETE FROM ?'

-- enable all constraints
EXEC sp_MSForEachTable 'ALTER TABLE ?
WITH CHECK CHECK CONSTRAINT all'


It may also be worth considering reseeding Identity columns that your tables
may have.

You can do that with this Statement:

EXEC sp_MSforEachTable 'DBCC CHECKIDENT ( '?', RESEED, 0)'

It must be noted however the different behaviour between a new table and a
table that has previously had data in it. See the below extract from BOL:

DBCC CHECKIDENT ('table_name', RESEED, newReseedValue)
The current identity value is set to the newReseedValue. If no rows have been inserted to the table since it was created, the first row inserted after executing DBCC CHECKIDENT will use newReseedValue as the identity. Otherwise, the next row inserted will use newReseedValue + 1. If the value of newReseedValue is less than the maximum value in the identity column, error message 2627 will be generated on subsequent references to the table.