rmaranhao
asked on
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?
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?
ASKER
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.
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.
You will need to code like
EXEC (@SSQL)
Instead of
Exec #SSQL
HTH
Namasi Navaretnam
EXEC (@SSQL)
Instead of
Exec #SSQL
HTH
Namasi Navaretnam
I meant to say instead of
Exec @SSQL try
Exec ( @SSQL)
Exec @SSQL try
Exec ( @SSQL)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot....
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