Truncate Multiple Tables with Dynamic SQL

I used the script below in an attempt to truncate a series of tables (in reality I'm attempting to truncate over 200 tables, I limited this example to 3).  Any ideas what I'm doing wrong?

The dynamic SQL should create a series like this and then execute:
truncate table DELETEMEtestbl1
truncate table DELETEMEtestbl2
truncate table DELETEMEtestbl3

Here's my botched attempt:
create table DELETEMEtestbl1 (AcctNum int)
create table DELETEMEtestbl2 (AcctNum int)
create table DELETEMEtestbl3 (AcctNum int)

declare @sSQL varchar(8000)
set @sSQL='
declare @counter int
set @counter=1
while @counter<3
begin
declare @counter2 varchar(3)
set @counter2=convert(varchar(3),@counter)
declare @TableName varchar(60)
set @TableName=''DELETEMEtestbl''+@counter2
truncate table @TableName
set @counter=@counter+1
end
'
exec(@sSQL)
LVL 3
srejjaAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hello srejja,

declare @sSQL varchar(8000)
declare @counter int
set @counter=1
while @counter<=3
begin
      declare @TableName varchar(60)
      set @TableName='DELETEMEtestbl'+convert(varchar(3),@counter)
      EXEC('truncate table '+@TableName)
      set @counter=@counter+1
end

Regards,

Aneesh
0
 
Anthony PerkinsCommented:
Or simply:
sp_msforeachtable
'IF EXISTS(SELECT 1 FROM sys.objects WHERE Type = ''U'' And Name LIKE ''DELETEMEtesttble_'')
      TRUNCATE TABLE [?]
'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.