How do you truncate all tables in a database using TSQL

Question

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

User · Answer

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

User · Answer

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

User · Answer

The hardest part of truncating all tables is removing and re-ading the foreign key constraints   The following query creates the drop  amp  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

User · Answer

The simplest way of doing this is to    open up SQL Management Studio navigate to your database Right-click and select Tasks- Generate Scripts  pic 1  On the  choose Objects  screen  select the  select specific objects  option and check  tables   pic 2  on the next screen  select  advanced  and then change the  Script DROP and CREATE  option to  Script DROP and CREATE   pic 3  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

For SQL 2005       EXEC sp MSForEachTable  TRUNCATE TABLE      Couple more links for 2000 and 2005 2008

User · Answer

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

User · Answer

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

User · Answer

It is a little late but it might help someone  I created a procedure sometimes back which does the following using T-SQL    Store all constraints in a Temporary table Drop All Constraints Truncate all tables with exception of some tables  which does not need truncation Recreate all Constraints    I have listed it on my blog here

User · Answer

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

User · Answer

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

User · Answer

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  lt   SELECT Count 1  FROM  DropFK  BEGIN     SELECT TOP 1  Cmd   Scripts      FROM  DropFK      EXEC   Cmd       DELETE  DropFK WHERE Scripts    Cmd END  WHILE 0  lt   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  lt   SELECT Count 1  FROM  CreateFK  BEGIN     SELECT TOP 1  Cmd   Scripts      FROM  CreateFK      EXEC   Cmd       DELETE  CreateFK WHERE Scripts    Cmd END

[sql-server] How do you truncate all tables in a database using TSQL?

Examples related to sql-server

Examples related to tsql