Using truncate table on all tables

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?

LVL 5
rmaranhaoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
dasariConnect With a Mentor Commented:
oops! it needs to be changed to EXEC(@SSQL).....
0
 
dasariCommented:
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
 
rmaranhaoAuthor Commented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
namasi_navaretnamCommented:
You will need to code like
EXEC (@SSQL)

Instead of
Exec #SSQL

HTH

Namasi Navaretnam
0
 
namasi_navaretnamCommented:
I meant to say instead of  
Exec @SSQL try

Exec ( @SSQL)

0
 
rmaranhaoAuthor Commented:
Thanks a lot....
0
All Courses

From novice to tech pro — start learning today.