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

When dealing with deleting data from tables which have foreign key relationships - which is basically the case with any properly designed database - we can disable all the constraints, delete all the data and then re-enable constraints

-- 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"

More on disabling constraints and triggers here

if some of the tables have identity columns we may want to reseed them

EXEC sp_MSForEachTable "DBCC CHECKIDENT ( '?', RESEED, 0)"

Note that the behaviour of RESEED differs between brand new table, and one which had had some data inserted previously 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.

Thanks to Robert for pointing out the fact that disabling constraints does not allow to use truncate, the constraints would have to be dropped, and then recreated

For SQL 2005,

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

Couple more links for 2000 and 2005/2008..

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 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=65341 and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957 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.

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.

This is one way to do it... there are likely 10 others that are better/more efficient, but it sounds like this is done very infrequently, so here goes...

get a list of the tables from sysobjects, then loop over those with a cursor, calling sp_execsql('truncate table ' + @table_name) for each iteration.

It is a little late but it might help someone. I created a procedure sometimes back which does the following using T-SQL:

1. Store all constraints in a Temporary table
2. Drop All Constraints
3. Truncate all tables with exception of some tables, which does not need truncation
4. Recreate all Constraints.

I have listed it on my blog here

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