?
Solved

Using truncate table on all tables

Posted on 2003-12-07
6
Medium Priority
?
1,033 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Suggested Courses

752 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