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

Question

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

Here's the king daddy of database wiping scripts. It will clear all tables and reseed them correctly:

SET QUOTED_IDENTIFIER ON;
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? NOCHECK CONSTRAINT ALL'  
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? DISABLE TRIGGER ALL'  
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; DELETE FROM ?'  
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? CHECK CONSTRAINT ALL'  
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ? ENABLE TRIGGER ALL' 
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON';

IF NOT EXISTS (
    SELECT
        *
    FROM
        SYS.IDENTITY_COLUMNS
        JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID
    WHERE
        SYS.TABLES.Object_ID = OBJECT_ID('?') AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL
)
AND OBJECTPROPERTY( OBJECT_ID('?'), 'TableHasIdentity' ) = 1

    DBCC CHECKIDENT ('?', RESEED, 0) WITH NO_INFOMSGS;

Enjoy, but be careful!

The simplest way of doing this is to

1. open up SQL Management Studio
2. navigate to your database
3. Right-click and select Tasks->Generate Scripts (pic 1)
4. On the "choose Objects" screen, select the "select specific objects" option and check "tables" (pic 2)
5. on the next screen, select "advanced" and then change the "Script DROP and CREATE" option to "Script DROP and CREATE" (pic 3)
6. Choose to save script to a new editor window or a file and run as necessary.

this will give you a script that drops and recreates all your tables without the need to worry about debugging or whether you've included everything. While this performs more than just a truncate, the results are the same. Just keep in mind that your auto-incrementing primary keys will start at 0, as opposed to truncated tables which will remember the last value assigned. You can also execute this from code if you don't have access to Management studio on your PreProd or Production environments.

1.

2.

3.

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.