Wednesday, April 10, 2013

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')


If you are searching life partner. your searching end with kpmarriage.com. now kpmarriage.com offer free matrimonial website which offer free message, free chat, free view contact information. so register here : kpmarriage.com- Free matrimonial website