CODEWITHSUNDEEP
sqltruncate
user2206834
user2206834

Reputation: 399

Truncate multiple tables with one SQL statement

Is there a possibility to truncate with one SQL statement, multiple tables?

Like this:

 truncate table #OBJ_AvailabilityTraining, #OBJ_AvailabilityHoliday, #Dates_temp;

Regards

Upvotes: 33

Views: 148194

Answers (6)

Xavi Duro
Xavi Duro

Reputation: 26

I have had to change the code sent by IndoKnight because it throws me an error in the truncate statement so I change the code by this, it is quite similar but the truncate part:

DECLARE @delimiter CHAR(1),
        @tableList VARCHAR(MAX),
        @tableName VARCHAR(100),
        @currLen INT,
        @truncateStatement VARCHAR(200)

SET @delimiter = ','

SELECT @tableList = COALESCE(@tableList + ', ','') + CAST (TABLE_NAME AS varchar(100))
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'xxx'

    WHILE LEN(@tableList) > 0
    BEGIN
        SELECT @currLen = 
        (
            CASE charindex( @delimiter, @tableList ) 
                WHEN 0 THEN len( @tableList  ) 
                ELSE ( charindex( @delimiter, @tableList  ) -1 )
            END
        ) 

        SELECT @tableName = TRIM(SUBSTRING (@tableList,1,@currLen ))

        SET @truncateStatement = 'TRUNCATE TABLE ' + QUOTENAME('xxx') + '.' + QUOTENAME(@tableName)
        EXEC (@truncateStatement)

        SELECT @tableList = 
        (
            CASE ( len( @tableList ) - @currLen  ) 
                WHEN 0 THEN '' 
                ELSE right( @tableList, len( @tableList ) - @currLen  - 1 ) 
            END
        ) 
    END

The trick is using QUOTENAME to put the square brackets to the table name and schema. Maybe IndoKnight solution does not work anymore in more recent version of the database. I hope it helps.

Upvotes: 1

Alfa Renaldo Aluska
Alfa Renaldo Aluska

Reputation: 179

Just easily enter this MySQL statement:

TRUNCATE TABLE yourtablename1;
TRUNCATE TABLE yourtablename2;
TRUNCATE TABLE yourtablename3;

... and so on.

Change the yourtablename with yours.

Don't forget the semicolon ;

Upvotes: 9

doublejosh
doublejosh

Reputation: 5774

Bonus example, truncate all tables in one database starting with a string...

SELECT CONCAT('TRUNCATE TABLE ', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'my_database_name'
  AND TABLE_NAME LIKE 'cache_%';

Upvotes: 3

IndoKnight
IndoKnight

Reputation: 1864

No, you can only truncate a single table with TRUNCATE command. To truncate multiple tables you can use T-SQL and iterate through table names to truncate each at a time.

DECLARE @delimiter CHAR(1),
        @tableList VARCHAR(MAX),
        @tableName VARCHAR(20),
        @currLen INT

SET @delimiter = ','

SET @tableList = 'table1,table2,table3'

WHILE LEN(@tableList) > 0
BEGIN
    SELECT @currLen = 
    (
        CASE charindex( @delimiter, @tableList ) 
            WHEN 0 THEN len( @tableList  ) 
            ELSE ( charindex( @delimiter, @tableList  ) -1 )
        END
    ) 

    SELECT @tableName = SUBSTRING (@tableList,1,@currLen )

    TRUNCATE TABLE @tableName

    SELECT tableList = 
    (
        CASE ( len( @tableList ) - @currLen  ) 
            WHEN 0 THEN '' 
            ELSE right( @tableList, len( @tableList ) - @currLen  - 1 ) 
        END
    ) 
END

You can have all your table names comma separated in @tableList variable and yes you can truncate multiple tables from different schemas if they are prefixed.

Upvotes: 15

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

No. But there's an alternative:

SELECT 'TRUNCATE TABLE '+TABLE_NAME+ ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'your-table-name%'

Example :

SELECT 'TRUNCATE TABLE '+TABLE_NAME+ ';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'TBL_ORDERS_20%'

Now you have below Results for above Select Query

TRUNCATE TABLE TBL_ORDERS_2001
TRUNCATE TABLE TBL_ORDERS_2002
TRUNCATE TABLE TBL_ORDERS_2003
TRUNCATE TABLE TBL_ORDERS_2004

or you can use something like

select 'Truncate table ' + TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_NAME in ('Table1', 'Table2')

Link 1

Link 2

Update:

Looking at table in your Example Query in your Question

truncate table #OBJ_AvailabilityTraining, #OBJ_AvailabilityHoliday, #Dates_temp

I think you want to Truncate all temporary tables.

You can do so with a simple Query like this

select 'Truncate table ' + TABLE_NAME from tempdb.INFORMATION_SCHEMA.TABLES

Upvotes: 10

Pankaj Agarwal
Pankaj Agarwal

Reputation: 11311

You can use the sp_MSforeachtable stored procedure like so:

USE MyDatabase
EXEC sp_MSforeachtable 'TRUNCATE TABLE ?'

Or you can create SQL Statement

SELECT concat('TRUNCATE TABLE ', TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'TableName%'

and run this above SQL statement

Upvotes: 14

Related Questions