Using Teradata SQL: How to drop (aka delete) a lot of tables at once

There are times when you might want to drop a load of tables from a database at once, rather than deleting them one at a time. This SQL finds all the tables that match the criteria, in this case the name of the user, and creates the commands that you'd need to use to DROP them. Note: All it does is create the commands, it doesn't run them so it's safe to play around with the code :-)

SELECT
    'DROP TABLE ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';'
FROM DBC.TABLESV
WHERE DATABASENAME = (SELECT USER)
    AND TABLEKIND = 'T';

Just cut and paste the results into the query area of SQL Assistant and run them.

There are ways you could automate this to delete the tables immediately, but I'm not going to cover those here - it's a little too dangerous for my tastes :-). You can always Google it if you really want to do it. Hint, it involves variables and EXECUTE IMMEDIATE.

You can modify this code to work with patterns if you want.

SELECT
    'DROP TABLE ' || TRIM(DATABASENAME) || '.' || TRIM(TABLENAME) || ';'
FROM DBC.TABLESV
WHERE DATABASENAME = 'Finance'
	AND TABLENAME LIKE 'OLDACCTS_%'
    AND TABLEKIND = 'T';

This would create the commands needed to drop all the tables in Finance that start with the name OLDACCTS_.

Source: Teradata Community

"Leadership has been defined as the ability to hide your panic from others." - Anonymous