SQL TRUNCATE DATABASE ? How to TRUNCATE ALL TABLES

database sql-server-2008 truncate
user609511 picture user609511 · May 17, 2011 · Viewed 115.2k times · Source

I use SQL server 2008 R2.

Is there a SQL command to empty the database, instead of having to truncate all 20 my tables?

I just want to delete the data not the structure.

Answer

Mark S. Rasmussen picture Mark S. Rasmussen · May 17, 2011

You can use the sp_MSforeachtable stored procedure like so:

USE MyDatabase
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

Be warned that this will delete (by truncation) ALL data from all user tables. And in case you can't TRUNCATE due to foreign keys etc. you can run the same as a delete:

USE MyDatabase
EXEC sp_MSforeachtable 'DELETE FROM ?'

Related questions

Reset identity seed after deleting records in SQL Server

I have inserted records into a SQL Server database table. The table had a primary key defined and the auto increment identity seed is set to “Yes”. This is done primarily because in SQL Azure, each table has to have …

Read More
how to schedule a job for sql query to run daily?

I need to know how to make a SQL query run daily using a SQL Server Agent job, with minimum required configuration settings.

Read More
Querying data by joining two tables in two database on different servers

There are two tables in two different databases on different servers, I need to join them so as to make few queries. What options do I have? What should I do?

Read More