CURSOR to delete from multiple table

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_DATAchange delete from [@.table_name] /* <--Does not recognize the
table_name variable at this point
to
exec('delete from ' + @.table_name)
"Troy Jerkins" <tjerkins@.alltel.net> wrote in message
news:ei%23SpdQRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> 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
>|||EXEC('delete from ' + @.table_name)
HH, Jens Suessmeyer.
http://sqlserver2005.de
--
"Troy Jerkins" <tjerkins@.alltel.net> schrieb im Newsbeitrag
news:ei%23SpdQRFHA.1172@.TK2MSFTNGP12.phx.gbl...
> 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
>|||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 Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:uEbLVlQRFHA.2664@.TK2MSFTNGP15.phx.gbl...
> EXEC('delete from ' + @.table_name)
> HH, Jens Suessmeyer.
> --
> http://sqlserver2005.de
> --
> "Troy Jerkins" <tjerkins@.alltel.net> schrieb im Newsbeitrag
> news:ei%23SpdQRFHA.1172@.TK2MSFTNGP12.phx.gbl...
>