Truncate multiple tables

_{Monday, February 18, 2013 12:39 PM}

Hello Ever one how to truncate multiple table at a time .. i need to truncate 240 tables it not possible to do each and every thing one by one any help

thx regards dinesh vv

_{Tuesday, February 19, 2013 3:20 AM ✅Answered | 1 vote}

Hi there,

use below script to truncate all data from all user tables.

If you have a specific list of tables then you required to insert in to the temp table:

SET NOCOUNT ON
declare @tablename table(id int identity(1,1),tname sysname)
declare @strtname sysname
declare @query varchar(max)
declare @total int
declare @cnt int
set @total = 0
set @cnt =1

--INSERT LIST OF YOUR TABLES IN TO THE TEMP TABLE
insert into @tablename
select name from sys.tables

select @total = (select COUNT(*) from @tablename)
while @cnt <= @total
begin
set @strtname = (select tname from @tablename where id = @cnt)
set @query = 'TRUNCATE TABLE [' + @strtname + ']'
EXEC(@query)
PRINT 'REMOVED DATA FROM :' + @strtname
set @cnt = @cnt + 1
end

thanks

kumar

_{Monday, February 18, 2013 1:00 PM}

Dinesh,

The quickest way is to generate script as follows, and then you can execute it once.

select 'Truncate table ' + st.name from sys.tables st where name in ('Table1', 'Table2')

Thanks
Manish

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

_{Monday, February 18, 2013 1:21 PM}

its not  working can

thx regards dinesh vv

_{Monday, February 18, 2013 1:42 PM}

its not  working can

thx regards dinesh vv

1. Could you please post the error you are getting.

2. Are you able to get the truncate table script for your table.

2. What is version of your SQL SERVER.

Thanks
Manish

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.