Macro to drop tables

[Queryman]

We have users who routinely go home without droping their tables from tempdb. This is a space that gets wiped clean every night at 4am. For users like myself this means that late night table creation is not possible due to lack of space til the space is wiped clean. I would like to create a macro that all users could run before they go home which would drop their tables. Is there a way to look for their usernames in the dbc tables and format a drop command on all their tables in the space tempdb. I am including a macro that exists that shows all the tables for all the users. We are on Teradata.

replace MACRO SYS_user_v.DatabaseUse (DBNAME CHAR(30) DEFAULT 'tempdb') AS (
select derived2.databasename,
derived2.tablename,
derived2.CurrPerm,
derived2.LastInsertDate,
TRIM(derived2.LastUpdatedBy) (title 'CreatorID'),
TRIM(c.commentstring) (TITLE 'CreatorName') (Char(60))
from
dbc.dbase c
RIGHT OUTER JOIN
(select
a.databasename (title 'db')
,a.tablename (title 'table')
,derived1.LastAlterName
,derived1.LastAlterTimeStamp
,sum(a.currentperm) (format 'zzz,zzz,zzz,zz9')
,sum(a.peakperm)
from
dbc.tablesize a
INNER JOIN
(sel databasename, creatorname, tablename, LastAlterTimeStamp
from dbc.tables
where databasename = BNAME)
derived1 (databasename, LastAlterName, tablename, LastAlterTimeStamp)
on a.databasename = derived1.databasename
and a.tablename = derived1.tablename
where a.databasename = BNAME
group by 1, 2, 3, 4)
derived2 (databasename, tablename, LastUpdatedBy, LastInsertDate, CurrPerm, PeakPerm)
c.databasenamei = derived2.LastUpdatedBy
order by 3 desc;


QueryMan

 

[r937]

queryman, that's not exactly an ansi sql question

perhaps you should post in forum328

oh, never mind, i see you already did


rudy