How do you truncate all tables in a database using TSQL?

I have a test environment for a database that I want to reload with new data at the start of a testing cycle. I am not interested in rebuilding the entire database- just simply "re-setting" the data.

What is the best way to remove all the data from all the tables using TSQL? Are there system stored procedures, views, etc. that can be used? I do not want to manually create and maintain truncate table statements for each table- I would prefer it to be dynamic.

For SQL 2005,

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

Couple more links for [2000][1] and [2005/2008][2]..


[1]:

[To see links please register here]

[2]:

[To see links please register here]

Truncating all of the tables will only work if you don't have any foreign key relationships between your tables, as SQL Server will not allow you to truncate a table with a foreign key.

An alternative to this is to determine the tables with foreign keys and delete from these first, you can then truncate the tables without foreign keys afterwards.

See

[To see links please register here]

and

[To see links please register here]

for further details.

Don't do this! Really, not a good idea.

If you know which tables you want to truncate, create a stored procedure which truncates them. You can fix the order to avoid foreign key problems.

If you really want to truncate them all (so you can BCP load them for example) you would be just as quick to drop the database and create a new one from scratch, which would have the additional benefit that you know exactly where you are.

I do not see why clearing data would be better than a script to drop and re-create each table.

That or keep a back up of your empty DB and restore it over old one

An alternative option I like to use with MSSQL Server Deveploper or Enterprise is to create a snapshot of the database immediately after creating the empty schema. At that point you can just keep restoring the database back to the snapshot.

It is much easier (and possibly even faster) to script out your database, then just drop and create it from the script.

Make an empty "template" database, take a full backup. When you need to refresh, just restore using WITH REPLACE. Fast, simple, bulletproof. And if a couple tables here or there need some base data(e.g. config information, or just basic information that makes your app run) it handles that too.

Before truncating the tables you have to remove all foreign keys. Use this [script][1] to generate final scripts to drop and recreate all foreign keys in database. Please set the @action variable to 'CREATE' or 'DROP'.


[1]:

[To see links please register here]

select 'delete from ' +TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='BASE TABLE'

where result come.

Copy and paste on query window and run the command