Solved

Using truncate table on all tables

Posted on 2003-12-07
6
1,024 Views
Last Modified: 2012-06-21
For testing purposes I need to wipe out all the data on my database. I have the following script:

WARNING: THIS SCRIPT IS SUPPOSED TO WIPE OUT ALL THE DATA IN THE DATABASE. DO NOT TRY TO EXECUTE IT UNLESS HAVE A DATABASE THAT YOU WANT TO BE ERASED.

create procedure sp_TruncateAll
as

DECLARE Tables_Cursor CURSOR FOR
  select name
  from SysObjects where type = 'U ' and Status > 0
  order by Name

Declare @TableName varchar(100)

OPEN Tables_Cursor
FETCH NEXT FROM Tables_Cursor into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
   truncate table @TableName
   ^^^^^^^^^^^^^^^^^^^^
   FETCH NEXT FROM Tables_Cursor into @TableName
END
CLOSE Tables_Cursor
DEALLOCATE Tables_Cursor

The problem is that the line truncate table @tablename generates an "Incorrect syntax" error when I try to create the procedure. Can anyone point me in the right direction?

0
Comment
Question by:rmaranhao
  • 2
  • 2
  • 2
6 Comments
 
LVL 4

Expert Comment

by:dasari
ID: 9892996
Instead of using it directly develop a dynamic SQL dude,

create procedure sp_TruncateAll
as

DECLARE Tables_Cursor CURSOR FOR
  select name
  from SysObjects where type = 'U ' and Status > 0
  order by Name

Declare @TableName varchar(100)
Declare @SSQL varchar(200)

OPEN Tables_Cursor
FETCH NEXT FROM Tables_Cursor into @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @SSQL = 'truncate table ' + @TableName
   EXEC @SSQL
   FETCH NEXT FROM Tables_Cursor into @TableName
END
CLOSE Tables_Cursor
DEALLOCATE Tables_Cursor

--
truncate table @TableName
   ^^^^^^^^^^^^^^^^^^^^

HTH
0
 
LVL 5

Author Comment

by:rmaranhao
ID: 9893037
This seems to be in the correct direction, but I got n (number of tables) messages like:

Server: Msg 2812, Level 16, State 62, Line 19
Could not find stored procedure 'truncate table EntidadeProduto'.
(EntidadeProduto is one of my tables.)

Thanks,
Roberto.

0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9893043
You will need to code like
EXEC (@SSQL)

Instead of
Exec #SSQL

HTH

Namasi Navaretnam
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9893047
I meant to say instead of  
Exec @SSQL try

Exec ( @SSQL)

0
 
LVL 4

Accepted Solution

by:
dasari earned 125 total points
ID: 9893056
oops! it needs to be changed to EXEC(@SSQL).....
0
 
LVL 5

Author Comment

by:rmaranhao
ID: 9893120
Thanks a lot....
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question