Truncate Dynamically Tables 1

[zakkar]

Hello to you all.
I'm trying to execute in Query Analyzer this script i have written

Use dokimastiki
go

Begin
Declare @stbname nvarchar(256)
Declare @smessage varchar(20)

DECLARE PKMS_Tables CURSOR FOR
select name from sysobjects
Where xtype = 'U'
OPEN PKMS_Tables
FETCH NEXT FROM PKMS_Tables INTO @stbname

WHILE @@FETCH_STATUS = 0
BEGIN
Case LTRIM(@stbname)
when 'replicas' then print @stbname
When 'replicas_codes_locations' then print @stbname
when 'replicas_locations' then print @stbname
when 'counters' then print @stbname
when 'danaos_supply_setting' then print @stbname
when 'dtproperties' then print @stbname
when 'tabledeleteno' then print @stbname
ELSE
BEGIN
Truncate Table LTRIM(@stbname)
END
END
FETCH NEXT FROM PKMS_Tables INTO @stbname
END
END

CLOSE PKMS_Tables
Deallocate PKMS_TAbles
Go

The case statement is for not to delete the specific tables
but truncate all the rests.The query when it is executed in Query analyzer shows me the following errors
Incorrect Syntax near the keyword 'Case'
line 24 : Incorrect syntax near '@stbname'
incorrect syntax near the keyword 'END'

All i'm trying to do is from a schema truncate all the tables except the table names that i have putted hardcoded inside my script.
Can anyone help me with this??
zkar programmer

 

[hmckillop]

replace your case with an IF statement e.g.

Use dokimastiki
go

Begin
Declare @stbname nvarchar(256)
Declare @smessage   varchar(20)

DECLARE PKMS_Tables CURSOR FOR 
  select name  from sysobjects 
   Where xtype = 'U'    
OPEN PKMS_Tables 
    FETCH NEXT FROM PKMS_Tables INTO @stbname 
    
    WHILE @@FETCH_STATUS = 0     
          BEGIN
        IF @stbname = 'replicas' OR @stbname = 'replicas_codes_locations' OR @stbname = 'replicas_locations' OR @stbname= 'counters' then @stbname = 'danaos_supply_setting' OR @stbname = 'dtproperties' OR @stbname = 'tabledeleteno' 
THEn 
BEGIN 
 Print 'Not these tables
END
         ELSE    
          BEGIN
        Truncate Table LTRIM(@stbname)    
          END
        END
         FETCH NEXT FROM PKMS_Tables INTO @stbname 
        END
END 

CLOSE PKMS_Tables
Deallocate PKMS_TAbles    
Go

"I'm living so far beyond my income that we may almost be said to be living apart

 

[JamesLean]

Or use the undocumented procedure sp_msforeachtable. This executes the given statement against every table in the database.

Note: tablenames are returned in the form [owner].

so you need to use this format when checking the names.

EXEC sp_msforeachtable 'IF ''?'' IN (''[dbo].[table1]'', ''[dbo].[table2]'') PRINT ''?'' ELSE TRUNCATE TABLE ?'

--James

 

[zakkar]

Still is not working hmckillop. Now as an error message i get line 18 .....The truncate syntax is wrong.What is wrong????
zkar programmer

 

[hmckillop]

Need to execute the command as you can run truncate table against a variable
so use

Use dokimastiki
go

Begin
Declare @stbname nvarchar(256)
Declare @smessage   varchar(20)
declare @sSQL varchar(255)

DECLARE PKMS_Tables CURSOR FOR 
  select name  from sysobjects 
   Where xtype = 'U'    
OPEN PKMS_Tables 
    FETCH NEXT FROM PKMS_Tables INTO @stbname 
    
    WHILE @@FETCH_STATUS = 0     
          BEGIN
        IF @stbname = 'replicas' OR @stbname = 'replicas_codes_locations' OR @stbname = 'replicas_locations' OR @stbname= 'counters' then @stbname = 'danaos_supply_setting' OR @stbname = 'dtproperties' OR @stbname = 'tabledeleteno' 
THEn 
BEGIN 
 Print 'Not these tables
END
         ELSE    
          BEGIN
        SET @sSQL ='Truncate Table ' + LTRIM(@stbname) 
        Exec (@sSQL)    
          END
        END
         FETCH NEXT FROM PKMS_Tables INTO @stbname 
        END
END 

CLOSE PKMS_Tables
Deallocate PKMS_TAbles    
Go

"I'm living so far beyond my income that we may almost be said to be living apart

 

[zakkar]

I figure that out .I cannot use the command Truncate Table @stbname.Yes but how can i implemment this case study i have??I also use this syntax

Set @SSqlstring = N'Truncate Table ' + Cast(@stbname as nvarchar(256))
Exec sq_executesql @SSqlString
Go

But still i get the same error.Incorrect syntax.
My use case needs to loop to the tables i found in sysobjects with xtype = 'U' and truncate some of them.
Please any ideas??????
Zkar
Programmer

 

[JamesLean]

Did you try my solution?

--James

 

[hmckillop]

When I use your code (apart from a typo for the stored procedure name you had "sq_executesql" it should be sp_executesql) - I added two variables, it works fine.

declare @stbname nvarchar(256), @sSQLString nvarchar(256)
SET @stbname = N'AccountTransaction'
Set @SSqlstring = N'Truncate Table  ' + Cast(@stbname as nvarchar(256))
Exec sp_executesql @SSqlString

As JamsLean says the for each table is a much tidier solution and doesnt use cursors.
so it would be

EXEC sp_msforeachtable 'IF ''?'' IN (''[dbo].[table1]'', ''[dbo].[table2]'') PRINT ''?'' ELSE TRUNCATE TABLE ?'

The only downside to this solution (probably applies to both), is that if you have any form of referential integrity and plan to truncate the data without first ensuring you have deleted the corresponding Foreign Key, you will then have errors. The cursor would allow you to handle these individually, one by one, whereas James solution wouldnt.



"I'm living so far beyond my income that we may almost be said to be living apart

 

[zakkar]

Still is not working.How can i write the second syntax.I want to truncate the tables found in sysobjects which have xtype = 'U' and from them i want to exclude some tables and truncate all others.
Can you help me syntax the second command???
Thank you
zkar programmer

 

[zakkar]

To jameslean.
I need some help to syntax your solution.
zkar programmer

 

[JamesLean]

I've given you the exact syntax. Obviously just replace table1, table2 with the names of your tables:

EXEC sp_msforeachtable 'IF ''?'' IN (''[dbo].[replicas]'', ''[dbo].[replicas_codes_locations]'', ''[dbo].[replicas_locations]'') PRINT ''?'' ELSE TRUNCATE TABLE ?'

--James

 

[miq]

Hi,

As far as referencing problem is concerned you can diable referential integrity by unchecking "Enforce constraint for INSERTs and UPDATEs".

I don't know any equivalent TSQL code.

B.R,
miq

 

[zakkar]

to jameslean
Thank you much.It worked for me.
I have although one final question.This script doesn't trucate system tables ok???I don't want to do that...As far as i saw the system tables remain as it were...Tell me if i'm wrong ok???

Zkar programmer

 

[JamesLean]

Nope - user tables only.

--James

 

[stevexff]

zakkar

Your last post is the kind of question you need to ask before you run the query, no?

 

[zakkar]

Yes.But already got my answer.Thank you stevexff