executing SQL stmts retrieved in a query

[ak]

Thanks for the previous info.

I ran the sql stmt:
SELECT sql = 'DROP TABLE' + name
FROM sysobjects
WHERE name like 'tmp%'
AND type = 'U'
It gives me a list that looks like the following

sys
---------------------------
DROP TABLE tmp8976
DROP TABLE tmp8977
etc..

What command do i use to run those drop table commands within sys to actually
do the deletion. Thank you very much for the help. Also what does the 'U'
stand for?

[DaveSatz]

see BOL on cursors for a general idea
DECLARE @sql varchar(1000)

DECLARE table_cursor CURSOR FOR
SELECT sql = 'DROP TABLE' + name
FROM sysobjects
WHERE name like 'tmp%'
AND type = 'U' -- U for user table
ORDER BY name

OPEN table_cursor


-- Perform the first fetch.

FETCH NEXT FROM table_cursor INTO @sql



-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC( @sql )

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM table_cursor INTO @sql
END

CLOSE table_cursor
DEALLOCATE table_cursor

--
Thanks,
David Satz
Principal Software Engineer
Hyperion Solutions
->Using SQL Server 7.0 SP2/6.5 SP5a/Cold Fusion 4.5.1/ADO 2.1/VB 6.0/MTS
(Please reply to group only)
-----------------------------------------------------------------

"ak" <alkotsmith@yahoo.com> wrote in message
news:3a6da2df$1@news.devx.com...
>
> Thanks for the previous info.
>
> I ran the sql stmt:
> SELECT sql = 'DROP TABLE' + name
> FROM sysobjects
> WHERE name like 'tmp%'
> AND type = 'U'
> It gives me a list that looks like the following
>
> sys
> ---------------------------
> DROP TABLE tmp8976
> DROP TABLE tmp8977
> etc..
>
> What command do i use to run those drop table commands within sys to
actually
> do the deletion. Thank you very much for the help. Also what does the
'U'
> stand for?
>
>