SQL TRUNCATE DATABASE How to TRUNCATE ALL TABLES

Question

SQL Server 2008 R2 is what I use. I don't want to have to truncate all 20 of my tables, but is there a SQL statement to empty the database?

I only want the data gone, not the structure.

Answer 1

Use the sp_MSforeachtable stored procedure like:

USE MyDatabase
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

Be aware that doing this will truncate all user tables' data and erase ALL of it. To perform the same as a deletion in the event that TRUNCATE is not possible due to foreign keys or another issue.

USE MyDatabase
EXEC sp_MSforeachtable 'DELETE FROM ?'