Skip to content

How to drop Teradata database with objects?

Dropping a database in Teradat is little different then how we do in Netezza, SQL Server, etc. In Teradata if you try to drop a database which have objects then you will get below error:
Drop database TEST_DB;

Executed as Single statement. Failed [3552 : HY000] Cannot DROP databases with tables, journal tables, views, or macros.
Elapsed time = 00:00:00.038

STATEMENT 1: DROP failed.
Reason you will get this error is because Teradat do not support dropping database which have any object in it. SO, here you have to delete all the tables, views, etc before dropping a database. Simple and easy way to do is by issuing below commands:
Delete database TEST_DB; — This will delete all the objects in the database

Executed as Single statement.
Elapsed time = 00:00:02.652

STATEMENT 1: Delete Statement completed. 0 rows processed.
Drop database TEST_DB; — Now since all objects were dropped by Delete command, this Drop command will run fine.

Executed as Single statement.
Elapsed time = 00:00:00.437

STATEMENT 1: DROP completed. 0 rows processed.