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

 

[dnoeth]

Impossible with a macro ;-(
But if you're running V2R4.1 you could use a Stored Procedure:
A cursor selecting the user's tablenames and for each name a call dbc.sysexecsql('drop table ' || tablename)

Depending on their needs maybe users could create volatile/global temporary tables, they're dropped automatically at logoff.

Dieter

 

[Queryman]

Thanks Dieter,
I am new to stored procedures, can you explain how they work and how something like what I am trying to achieve could be coded in a stored procedure?

QueryMan

 

[dnoeth]

For details about SPs look at the SQL manual Vol. 6

Next SP must be created from a user in it's own permspace or it will not work, e.g.
user sysdba submits a
REPLACE PROCEDURE sysdba.CleanTempDB

then followed by a
grant execute procedure on CleanTempDB to anytempdbuser;

And anytempdbuser executes a
call CleanTempDB();

Dieter

REPLACE PROCEDURE CleanTempDB ()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
/*** table couldn't be dropped, just skip it ***/
END;

/*** select all tables of the current user ***/
FOR del_cursor AS
LOCKING dbc.tables FOR ACCESS
SELECT
'drop table '
|| TRIM(databasename) || '.'
|| TRIM( tablename) || ';' AS DropCommand
FROM dbc.tables
WHERE tablekind = 't'
AND creatorname = user
AND databasename = 'tempDB'
DO
/*** and drop each table ***/
CALL dbc.sysexecsqldel_cursor.DropCommand);
END FOR;

END;

 

[Queryman]

Thanks! Dieter.

QueryMan