Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using truncate table on all tables

Posted on 2003-12-07
6
Medium Priority
?
1,039 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

636 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