SQL TRUNCATE DATABASE ? How to TRUNCATE ALL TABLES

Question

I use SQL server 2008 R2.

Is there a SQL command to empty the database, instead of having to truncate all 20 my tables?

I just want to delete the data not the structure.

While a valid question, obviously any answer is such that it would involve a dangerous entity (such as a stored proc) to have around -- God help someone who runs it accidently! — JosephDoggie, Apr 29 '16 at 13:41

score 47 · Accepted Answer · answered May 17 '11 at 10:46

47

You can use the sp_MSforeachtable stored procedure like so:

USE MyDatabase
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

Be warned that this will delete (by truncation) ALL data from all user tables. And in case you can't TRUNCATE due to foreign keys etc. you can run the same as a delete:

USE MyDatabase
EXEC sp_MSforeachtable 'DELETE FROM ?'

answered May 17 '11 at 10:46

Mark S. Rasmussen

34,696
4
39
58

5

This will not work if any of the tables have foreign keys. These will need to be dropped and re-created (not disabled/enabled) – Martin Smith May 17 '11 at 10:48
2

@Martin I added that info simultaneously to your comment :) Though you're right that you will need to delete in the correct order unless there's cascading update/deletes. Otherwise they'll have to be disabled beforehand, as you mention. – Mark S. Rasmussen May 17 '11 at 10:49
Agreed, disabling them will work for the `DELETE` but not `TRUNCATE` – Martin Smith May 17 '11 at 10:53
1

How about just running it multiple times? :) – cairnz May 20 '11 at 12:57
2

@cairnz that isn't a very good long term solution. If you are trying to automate this process, you may never know how many times it needs to run in advance. For this purpose, Boycs' answer is much more effective because it disables your foreign keys, empties the table, and then re-enables they keys. – mtmurdock Oct 04 '12 at 23:22
Completely agree, but for automation you get a more rigid structure. This was asked sounding like an adhoc solution to just "get rid of all my data". :) – cairnz Oct 05 '12 at 12:06

score 46 · Answer 2 · answered May 01 '12 at 20:39

46

I use this script

EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘DELETE FROM ?’
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO

answered May 01 '12 at 20:39

Boycs

5,610
2
28
23

10

LOVE IT. This is great. I would only add to this a call to `dbcc checkident (?, RESEED, 1)` to reset the identities (for those tables that have identities). – mtmurdock Oct 04 '12 at 23:23
1

In SQL Server 2012, I had to use @command1 = after the stored proc name and had to use double quotes instead of single around the acctual command. – jtrohde Dec 18 '15 at 19:11

score 6 · Answer 3 · edited May 23 '17 at 12:18

The accepted answer doesn't quite work when you have foreign key relationships. In that case you have to drop the constraints and recreate them. Below is a stored proc for doing that based on the answer here

CREATE PROCEDURE [dbo].[deleteAllDataFromAllTables] AS
SET NOCOUNT ON

    DECLARE @dropAndCreateConstraintsTable TABLE ( DropStmt varchar(max) , CreateStmt varchar(max) )

    insert @dropAndCreateConstraintsTable select
      DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + 
          '].[' + ForeignKeys.ForeignTableName + 
          '] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; '
    ,  CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + 
          '].[' + ForeignKeys.ForeignTableName + 
          '] WITH CHECK ADD CONSTRAINT [' +  ForeignKeys.ForeignKeyName + 
          '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn + 
          ']) REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
      sys.objects.[name] + ']([' +
      sys.columns.[name] + ']); '
     from sys.objects
      inner join sys.columns
        on (sys.columns.[object_id] = sys.objects.[object_id])
      inner join (
        select sys.foreign_keys.[name] as ForeignKeyName
         ,schema_name(sys.objects.schema_id) as ForeignTableSchema
         ,sys.objects.[name] as ForeignTableName
         ,sys.columns.[name]  as ForeignTableColumn
         ,sys.foreign_keys.referenced_object_id as referenced_object_id
         ,sys.foreign_key_columns.referenced_column_id as referenced_column_id
         from sys.foreign_keys
          inner join sys.foreign_key_columns
            on (sys.foreign_key_columns.constraint_object_id
              = sys.foreign_keys.[object_id])
          inner join sys.objects
            on (sys.objects.[object_id]
              = sys.foreign_keys.parent_object_id)
            inner join sys.columns
              on (sys.columns.[object_id]
                = sys.objects.[object_id])
               and (sys.columns.column_id
                = sys.foreign_key_columns.parent_column_id)
        ) ForeignKeys
        on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
         and (ForeignKeys.referenced_column_id = sys.columns.column_id)
     where (sys.objects.[type] = 'U')
      and (sys.objects.[name] not in ('sysdiagrams'))


    DECLARE @DropStatement nvarchar(max)
    DECLARE @RecreateStatement nvarchar(max)

    DECLARE C1 CURSOR READ_ONLY
    FOR
    SELECT DropStmt from @dropAndCreateConstraintsTable
    OPEN C1

    FETCH NEXT FROM C1 INTO @DropStatement

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Executing ' + @DropStatement
        execute sp_executesql @DropStatement
        FETCH NEXT FROM C1 INTO @DropStatement
    END
    CLOSE C1
    DEALLOCATE C1

    DECLARE @DeleteTableStatement nvarchar(max)
    DECLARE C2 CURSOR READ_ONLY
    FOR
    SELECT 'Delete From [dbo].[' + TABLE_NAME + ']' from INFORMATION_SCHEMA.TABLES 
        WHERE TABLE_SCHEMA = 'dbo' -- Change your schema appropriately.
    OPEN C2

    FETCH NEXT FROM C2 INTO @DeleteTableStatement

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Executing ' + @DeleteTableStatement
        execute sp_executesql @DeleteTableStatement
        FETCH NEXT FROM C2 INTO  @DeleteTableStatement
    END
    CLOSE C2
    DEALLOCATE C2

    DECLARE C3 CURSOR READ_ONLY
    FOR
    SELECT CreateStmt from @dropAndCreateConstraintsTable
    OPEN C3

    FETCH NEXT FROM C3 INTO @RecreateStatement

    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Executing ' + @RecreateStatement
        execute sp_executesql @RecreateStatement
        FETCH NEXT FROM C3 INTO  @RecreateStatement
    END
    CLOSE C3
    DEALLOCATE C3

GO

score 6 · Answer 4 · answered Mar 14 '14 at 15:26

Most of the solutions provided either don't use TRUNCATE, which is different from DELETE, or they don't deal with the issue of foreign key constraints. The solution provided by Chaitanya comes close, but it falls back to using DELETE, and it does it in a stored procedure, which seems to not fit a situation where you are nuking all data in a database.

So, below is my variation which does use TRUNCATE and does address the foreign key constraint problem.

    /* TRUNCATE ALL TABLES IN A DATABASE */

DECLARE @dropAndCreateConstraintsTable TABLE
        (
         DropStmt VARCHAR(MAX)
        ,CreateStmt VARCHAR(MAX)
        )
/* Gather information to drop and then recreate the current foreign key constraints  */
INSERT  @dropAndCreateConstraintsTable
        SELECT  DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
                + '].[' + ForeignKeys.ForeignTableName + '] DROP CONSTRAINT ['
                + ForeignKeys.ForeignKeyName + ']; '
               ,CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema
                + '].[' + ForeignKeys.ForeignTableName
                + '] WITH CHECK ADD CONSTRAINT [' + ForeignKeys.ForeignKeyName
                + '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn
                + ']) REFERENCES [' + SCHEMA_NAME(sys.objects.schema_id)
                + '].[' + sys.objects.[name] + ']([' + sys.columns.[name]
                + ']); '
        FROM    sys.objects
        INNER JOIN sys.columns
                ON ( sys.columns.[object_id] = sys.objects.[object_id] )
        INNER JOIN ( SELECT sys.foreign_keys.[name] AS ForeignKeyName
                           ,SCHEMA_NAME(sys.objects.schema_id) AS ForeignTableSchema
                           ,sys.objects.[name] AS ForeignTableName
                           ,sys.columns.[name] AS ForeignTableColumn
                           ,sys.foreign_keys.referenced_object_id AS referenced_object_id
                           ,sys.foreign_key_columns.referenced_column_id AS referenced_column_id
                     FROM   sys.foreign_keys
                     INNER JOIN sys.foreign_key_columns
                            ON ( sys.foreign_key_columns.constraint_object_id = sys.foreign_keys.[object_id] )
                     INNER JOIN sys.objects
                            ON ( sys.objects.[object_id] = sys.foreign_keys.parent_object_id )
                     INNER JOIN sys.columns
                            ON ( sys.columns.[object_id] = sys.objects.[object_id] )
                               AND ( sys.columns.column_id = sys.foreign_key_columns.parent_column_id )
                   ) ForeignKeys
                ON ( ForeignKeys.referenced_object_id = sys.objects.[object_id] )
                   AND ( ForeignKeys.referenced_column_id = sys.columns.column_id )
        WHERE   ( sys.objects.[type] = 'U' )
                AND ( sys.objects.[name] NOT IN ( 'sysdiagrams' ) )

/* SELECT * FROM @dropAndCreateConstraintsTable AS DACCT */

DECLARE @DropStatement NVARCHAR(MAX)
DECLARE @RecreateStatement NVARCHAR(MAX)

/* Drop Constraints */
DECLARE C1 CURSOR READ_ONLY
FOR
        SELECT  DropStmt
        FROM    @dropAndCreateConstraintsTable
OPEN C1

FETCH NEXT FROM C1 INTO @DropStatement

WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @DropStatement
            EXECUTE sp_executesql @DropStatement
            FETCH NEXT FROM C1 INTO @DropStatement
      END
CLOSE C1
DEALLOCATE C1

/* Truncate all tables in the database in the dbo schema */
DECLARE @DeleteTableStatement NVARCHAR(MAX)
DECLARE C2 CURSOR READ_ONLY
FOR
        SELECT  'TRUNCATE TABLE [dbo].[' + TABLE_NAME + ']'
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_SCHEMA = 'dbo'
                AND TABLE_TYPE = 'BASE TABLE'
  /* Change your schema appropriately if you don't want to use dbo */
OPEN C2

FETCH NEXT FROM C2 INTO @DeleteTableStatement

WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @DeleteTableStatement
            EXECUTE sp_executesql @DeleteTableStatement
            FETCH NEXT FROM C2 INTO @DeleteTableStatement
      END
CLOSE C2
DEALLOCATE C2

/* Recreate foreign key constraints  */
DECLARE C3 CURSOR READ_ONLY
FOR
        SELECT  CreateStmt
        FROM    @dropAndCreateConstraintsTable
OPEN C3

FETCH NEXT FROM C3 INTO @RecreateStatement

WHILE @@FETCH_STATUS = 0
      BEGIN
            PRINT 'Executing ' + @RecreateStatement
            EXECUTE sp_executesql @RecreateStatement
            FETCH NEXT FROM C3 INTO @RecreateStatement
      END
CLOSE C3
DEALLOCATE C3

GO

Dave, is there a way to determine the `on delete` and `on update` properties when dropping and recreating the foreign keys? If I were to ignore them, as you have done, would they restore their previous settings? I have some set to `cascade` and some to `set null`. — Michael, May 29 '14 at 14:42
Note to future visitors: they can be found in the `sys.foreign_keys` table. ([Reference](http://technet.microsoft.com/en-us/library/ms186973(v=sql.105).aspx)) — Michael, May 29 '14 at 19:40

score 4 · Answer 5 · answered Nov 06 '14 at 10:59

execute this

EXEC sp_MSforeachtable 'PRINT ''ALTER TABLE ? NOCHECK CONSTRAINT ALL'''
EXEC sp_MSforeachtable 'print ''DELETE FROM ?'''
EXEC sp_MSforeachtable 'print ''ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'''

After copy the printed result and paste it on Query field and Execute it. It will truncate all tables.

score 2 · Answer 6 · edited Jan 23 '14 at 05:24

2

DECLARE @nombre NVARCHAR(100)
DECLARE @tablas TABLE(nombre nvarchar(100))

INSERT INTO @tablas
SELECT t.TABLE_SCHEMA+ '.'+t.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES T
DECLARE @contador INT=0

SELECT @contador=COUNT(*) FROM INFORMATION_SCHEMA.TABLES

WHILE @contador>0 
BEGIN
    SELECT TOP 1 @nombre=nombre FROM @tablas 
    DECLARE @sql NVARCHAR(500)=''
    SET @sql =@sql+'Truncate table  '+@nombre
    EXEC (@sql)
    SELECT @sql
    SET @contador=@contador-1   
    DELETE TOP (1) @tablas 
END

edited Jan 23 '14 at 05:24

digitalextremist

5,952
3
43
62

answered Jan 23 '14 at 05:01

user3226298

21
1

2

Describe your query a bit? – digitalextremist Jan 23 '14 at 05:21

score 2 · Answer 7 · answered Jan 15 '15 at 05:53

No nonsense script this:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSforeachtable 'DBCC CHECKIDENT ( ''?'', RESEED, 0)'
GO

Truncate will still not work if you have foreign keys in tables. This script will reset all identity columns as well.

score 2 · Answer 8 · answered Feb 05 '19 at 12:58

---- Remove Constraint ----
EXEC sp_MSForEachTable "ALTER TABLE ? NOCHECK CONSTRAINT all"

---- Delete Data ----
EXEC sp_MSForEachTable "DELETE FROM ?"

---- Add Constraint ----
EXEC sp_MSForEachTable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

---- Reset Identity value ----
EXEC sp_MSForEachTable "DBCC CHECKIDENT ( '?', RESEED, 0)"

score 1 · Answer 9 · answered May 30 '14 at 19:18

Taking a point from both Boycs Answer and mtmurdock's subsequent answer I have the following stored proc on all of my development or staging databases. I've added some switches to fit my own requirement if I need to add in statements to reseed the data for certain columns.

(Note: I would have added this as a comment to Boycs brilliant answer but I haven't got enough reputation to do that yet. So please accept my apologies for adding this as an entirely new answer.)

ALTER PROCEDURE up_ResetEntireDatabase
@IncludeIdentReseed BIT,
@IncludeDataReseed BIT
AS

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'

IF @IncludeIdentReseed = 1
BEGIN
    EXEC sp_MSForEachTable 'DBCC CHECKIDENT (''?'' , RESEED, 1)'
END

EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

IF @IncludeDataReseed = 1
BEGIN
    -- Populate Core Data Table Here
END
GO

And then once ready the execution is really simple:

EXEC up_ResetEntireDatabase 1, 1

score 0 · Answer 10 · answered May 13 '16 at 14:54

You can also use this stored procedure if you only want to truncate all tables in a specific schema:

-- =============================================
-- Author:      Ben de Ridder
-- Create date: 20160513
-- Description: Truncate all tables in schema
-- =============================================
CREATE PROCEDURE TruncateAllTablesInSchema
    @schema nvarchar(50)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @table nVARCHAR(255)

    DECLARE db_cursor CURSOR FOR 
        select t.name
          from sys.tables t inner join
               sys.schemas s on 
                    t.schema_id=s.schema_id
         where s.name=@schema
         order by 1

    OPEN db_cursor   

    FETCH NEXT FROM db_cursor INTO @table   

    WHILE @@FETCH_STATUS = 0   
    BEGIN   
           declare @sql nvarchar(1000)

           set @sql = 'truncate table [' + @schema + '].[' + @table + ']'

           exec sp_sqlexec @sql

           FETCH NEXT FROM db_cursor INTO @table   
    END   

    CLOSE db_cursor   
    DEALLOCATE db_cursor 

END
GO

score 0 · Answer 11 · answered Jan 03 '21 at 17:10

0

As my purpose is to get an empty version of the test database to import data from an external previous current active source (Access database) once all is fine tuned. I found that using DBCC CloneDatabase with Verify_CloneDB option fits perfectly.

answered Jan 03 '21 at 17:10

Andre Gotlieb

27
1
7

score 0 · Answer 12 · edited Oct 22 '21 at 20:39

0

Go to Database Structure -> Select All -> EMPTY

It'll Execute the TRUNCATE for all tables, Just HIT OK.

edited Oct 22 '21 at 20:39

Ralf

16,086
4
44
68

answered Oct 22 '21 at 19:46

niranjan hiremath

1

SQL TRUNCATE DATABASE ? How to TRUNCATE ALL TABLES

12 Answers12