TRUNCATE MULTIPLE TABLE

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

Like this:

truncate table #OBJ_AvailabilityTraining, #OBJ_AvailabilityHoliday, #Dates_temp;

Ans1 :

  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.

Ans2 : 

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

Ans 3 :

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