[sql-server] 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.

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.

The hardest part of truncating all tables is removing and re-ading the foreign key constraints.

The following query creates the drop & create statements for each constraint relating to each table name in @myTempTable. If you would like to generate these for all the tables, you may simple use information schema to gather these table names instead.

DECLARE @myTempTable TABLE (tableName varchar(200))
INSERT INTO @myTempTable(tableName) VALUES
('TABLE_ONE'),
('TABLE_TWO'),
('TABLE_THREE')


-- DROP FK Contraints
SELECT 'alter table '+quotename(schema_name(ob.schema_id))+
  '.'+quotename(object_name(ob.object_id))+ ' drop constraint ' + quotename(fk.name) 
  FROM sys.objects ob INNER JOIN sys.foreign_keys fk ON fk.parent_object_id = ob.object_id
  WHERE fk.referenced_object_id IN 
      (
         SELECT so.object_id 
         FROM sys.objects so JOIN sys.schemas sc
         ON so.schema_id = sc.schema_id
         WHERE so.name IN (SELECT * FROM @myTempTable)  AND sc.name=N'dbo'  AND type in (N'U'))


 -- CREATE FK Contraints
 SELECT 'ALTER TABLE [PIMSUser].[dbo].[' +cast(c.name as varchar(255)) + '] WITH NOCHECK ADD CONSTRAINT ['+ cast(f.name as varchar(255)) +'] FOREIGN KEY (['+ cast(fc.name as varchar(255)) +'])
      REFERENCES [PIMSUser].[dbo].['+ cast(p.name as varchar(255)) +'] (['+cast(rc.name as varchar(255))+'])'
FROM  sysobjects f
      INNER JOIN sys.sysobjects c ON f.parent_obj = c.id
      INNER JOIN sys.sysreferences r ON f.id = r.constid
      INNER JOIN sys.sysobjects p ON r.rkeyid = p.id
      INNER JOIN sys.syscolumns rc ON r.rkeyid = rc.id and r.rkey1 = rc.colid
      INNER JOIN sys.syscolumns fc ON r.fkeyid = fc.id and r.fkey1 = fc.colid
WHERE 
      f.type = 'F'
      AND
      cast(p.name as varchar(255)) IN (SELECT * FROM @myTempTable)

I then just copy out the statements to run - but with a bit of dev effort you could use a cursor to run them dynamically.

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!

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.

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

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.

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.

Run the commented out section once, populate the _TruncateList table with the tables you want truncated, then run the rest of the script. The _ScriptLog table will need to be cleaned up over time if you do this a lot.

You can modify this if you want to do all tables, just put in SELECT name INTO #TruncateList FROM sys.tables. However, you usually don't want to do them all.

Also, this will affect all foreign keys in the database, and you can modify that as well if it's too blunt-force for your application. It's not for my purposes.

/*
CREATE TABLE _ScriptLog 
(
    ID Int NOT NULL Identity(1,1)
    , DateAdded DateTime2 NOT NULL DEFAULT GetDate()
    , Script NVarChar(4000) NOT NULL
)

CREATE UNIQUE CLUSTERED INDEX IX_ScriptLog_DateAdded_ID_U_C ON _ScriptLog
(
    DateAdded
    , ID
)

CREATE TABLE _TruncateList
(
    TableName SysName PRIMARY KEY
)
*/
IF OBJECT_ID('TempDB..#DropFK') IS NOT NULL BEGIN
    DROP TABLE #DropFK
END

IF OBJECT_ID('TempDB..#TruncateList') IS NOT NULL BEGIN
    DROP TABLE #TruncateList
END

IF OBJECT_ID('TempDB..#CreateFK') IS NOT NULL BEGIN
    DROP TABLE #CreateFK
END

SELECT Scripts = 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+
' DROP  CONSTRAINT ' + '[' + f.name  + ']'
INTO #DropFK
FROM .sys.foreign_keys AS f
INNER JOIN .sys.foreign_key_columns AS fc
ON f.OBJECT_ID = fc.constraint_object_id

SELECT TableName
INTO #TruncateList
FROM _TruncateList

SELECT Scripts = 'ALTER TABLE ' + const.parent_obj + '
    ADD CONSTRAINT ' + const.const_name + ' FOREIGN KEY (
            ' + const.parent_col_csv + '
            ) REFERENCES ' + const.ref_obj + '(' + const.ref_col_csv + ')
'
INTO #CreateFK
FROM (
    SELECT QUOTENAME(fk.NAME) AS [const_name]
        ,QUOTENAME(schParent.NAME) + '.' + QUOTENAME(OBJECT_name(fkc.parent_object_id)) AS [parent_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcP.parent_object_id, fcp.parent_column_id))
                FROM sys.foreign_key_columns AS fcP
                WHERE fcp.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [parent_col_csv]
        ,QUOTENAME(schRef.NAME) + '.' + QUOTENAME(OBJECT_NAME(fkc.referenced_object_id)) AS [ref_obj]
        ,STUFF((
                SELECT ',' + QUOTENAME(COL_NAME(fcR.referenced_object_id, fcR.referenced_column_id))
                FROM sys.foreign_key_columns AS fcR
                WHERE fcR.constraint_object_id = fk.object_id
                FOR XML path('')
                ), 1, 1, '') AS [ref_col_csv]
    FROM sys.foreign_key_columns AS fkc
    INNER JOIN sys.foreign_keys AS fk ON fk.object_id = fkc.constraint_object_id
    INNER JOIN sys.objects AS oParent ON oParent.object_id = fkc.parent_object_id
    INNER JOIN sys.schemas AS schParent ON schParent.schema_id = oParent.schema_id
    INNER JOIN sys.objects AS oRef ON oRef.object_id = fkc.referenced_object_id
    INNER JOIN sys.schemas AS schRef ON schRef.schema_id = oRef.schema_id
    GROUP BY fkc.parent_object_id
        ,fkc.referenced_object_id
        ,fk.NAME
        ,fk.object_id
        ,schParent.NAME
        ,schRef.NAME
    ) AS const
ORDER BY const.const_name

INSERT INTO _ScriptLog (Script)
SELECT Scripts
FROM #CreateFK

DECLARE @Cmd NVarChar(4000)
    , @TableName SysName

WHILE 0 < (SELECT Count(1) FROM #DropFK) BEGIN
    SELECT TOP 1 @Cmd = Scripts 
    FROM #DropFK

    EXEC (@Cmd)

    DELETE #DropFK WHERE Scripts = @Cmd
END

WHILE 0 < (SELECT Count(1) FROM #TruncateList) BEGIN
    SELECT TOP 1 @Cmd = N'TRUNCATE TABLE ' +  TableName
        , @TableName = TableName
    FROM #TruncateList

    EXEC (@Cmd)

    DELETE #TruncateList WHERE TableName = @TableName
END

WHILE 0 < (SELECT Count(1) FROM #CreateFK) BEGIN
    SELECT TOP 1 @Cmd = Scripts 
    FROM #CreateFK

    EXEC (@Cmd)

    DELETE #CreateFK WHERE Scripts = @Cmd
END

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

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.

If you want to keep data in a particular table (i.e. a static lookup table) while deleting/truncating data in other tables within the same db, then you need a loop with the exceptions in it. This is what I was looking for when I stumbled onto this question.

sp_MSForEachTable seems buggy to me (i.e. inconsistent behavior with IF statements) which is probably why its undocumented by MS.

declare @LastObjectID int = 0
declare @TableName nvarchar(100) = ''
set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id])
while(@LastObjectID is not null)
begin
    set @TableName = (select top 1 [name] from sys.tables where [object_id] = @LastObjectID)

    if(@TableName not in ('Profiles', 'ClientDetails', 'Addresses', 'AgentDetails', 'ChainCodes', 'VendorDetails'))
    begin
        exec('truncate table [' + @TableName + ']')
    end 

    set @LastObjectID = (select top 1 [object_id] from sys.tables where [object_id] > @LastObjectID order by [object_id])
end

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

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

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