I'm trying to declare a cursor that gets a list of tables in a databse and
then deletes all the data from them. My problem is that SQL will not
recognize
the variable as a table name. Is there another way to do this or perhaps
some different syntax use?

Here are my statements

declare @table_name nvarchar(384)
DECLARE DEL_WO_DATA CURSOR FOR
select [name] As table_name from sysobjects where [name] like 'work_%'


OPEN DEL_WO_DATA

FETCH NEXT FROM DEL_WO_DATA
INTO @table_name
WHILE (@@FETCH_STATUS = 0)
BEGIN

delete from [@table_name] /* <--Does not recognize the @table_name
variable at this point

Receive this error while debugging:

Server: Msg 208, Level 16, State 1, Procedure TEST, Line 33
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name
'@table_Name'. */

FETCH NEXT FROM DEL_WO_DATA
INTO @table_name

end
close DEL_WO_DATA
deallocate DEL_WO_DATA

change delete from [@table_name] /* <--Does not recognize the
table_name variable at this point

to

exec('delete from ' + @table_name)



"Troy Jerkins" <tjerkins (AT) alltel (DOT) net> wrote

EXEC('delete from ' + @table_name)

HH, Jens Suessmeyer.

---
http://sqlserver2005.de
---

"Troy Jerkins" <tjerkins (AT) alltel (DOT) net> schrieb im Newsbeitrag
news:ei%23SpdQRFHA.1172 (AT) TK2MSFTNGP12 (DOT) phx.gbl...

Try the script below. However this will most likely fail if you have
foreign key constraints declared on tables. If this is just a one-off
then you could also try:

/* Are you SURE?? EXEC sp_msforeachtable 'DELETE ?' */

Again, you may have to execute it more than once if it fails on FK
constraints. Don't use this in persistent code as it's undocumented.

You may alternatively find it more efficient just to drop the database
and then re-create it from a script.

/* DELETE FROM every table !! */

DECLARE @TableName SYSNAME
DECLARE TableList INSENSITIVE CURSOR FOR
SELECT table_name FROM information_schema.tables WHERE
table_type='BASE TABLE'

OPEN TableList
FETCH TableList INTO @TableName

WHILE @@fetch_status=0
BEGIN
PRINT @TableName
/* Are you SURE?? EXEC ('DELETE FROM ['+@TableName+']') */
FETCH TableList INTO @TableName
END

CLOSE TableList
DEALLOCATE TableList

--
David Portas
SQL Server MVP
--

That worked! Many thanks... to both of you.

-Troy

"Jens Süßmeyer" <Jens (AT) Remove_this_For_Contacting (DOT) sqlserver2005.de> wrote in
message news:uEbLVlQRFHA.2664 (AT) TK2MSFTNGP15 (DOT) phx.gbl...