srejja
asked on
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(
declare @TableName varchar(60)
set @TableName=''DELETEMEtestb
truncate table @TableName
set @counter=@counter+1
end
'
exec(@sSQL)
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(
declare @TableName varchar(60)
set @TableName=''DELETEMEtestb
truncate table @TableName
set @counter=@counter+1
end
'
exec(@sSQL)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
sp_msforeachtable
'IF EXISTS(SELECT 1 FROM sys.objects WHERE Type = ''U'' And Name LIKE ''DELETEMEtesttble_'')
TRUNCATE TABLE [?]
'