Delete Guest Owned Tables

[sqldba20]

I am trying to get guest owned tables deleted from tempdb database if exists and which are created more than 8 or 9 hours ago. Is this possible as a query in sql 2000?



Thanks !

[iburyak]

You can generate script with following statement:

use tempdb
go
select 'drop table ' + name from tempdb..sysobjects where crdate < dateadd(hh, -9, getdate())

Good Luck.

[ck9663]

I am trying to get guest owned tables deleted from tempdb database if exists and which are created more than 8 or 9 hours ago. Is this possible as a query in sql 2000?



Thanks !

this query will give you a list of tables created 8 (and later) hours ago

select * from sysobjects
where uid = 2 and xtype = 'U' and
datediff(hour, crdate, getdate()) > = 8


you can create a cursor based on this query...build your DROP TABLE sql statement in a string using a variable for the name of the object and do an EXEC