drop database in teradata

we can clear a database in teradata by a "delete" easily, not have to delete all tables and views one by one.

eg.
delete database db01;
drop database db01;


as we know we can't drop a database with the any objects under it , eg table, view , trigger and so on. we need to clear a database before we drop it.

so we can delete all object under a database by run the following sql's result


sel 'drop trigger ' || trim(databasename) || '.' || trim(tablename) || ';' from dbc.tables  where databasename = 'hst_db' and tablekind = 'G'
union all
sel 'drop view ' || trim(databasename) || '.' || trim(tablename) || ';' from dbc.tables  where databasename = 'hst_db' and tablekind = 'V'
union all
sel 'drop procedure ' || trim(databasename) || '.' || trim(tablename) || ';' from dbc.tables  where databasename = 'hst_db' and tablekind = 'P'
union all
sel 'drop table ' || trim(databasename) || '.' || trim(tablename) || ';' from dbc.tables  where databasename = 'hst_db' and tablekind = 'T'

Tweet