Dropping tables

A table can be dropped with a DROP TABLE statement.

About this task

When a table is dropped, the row in the SYSCAT.TABLES system catalog view that contains information about that table is dropped, and any other objects that depend on the table are affected. For example:

All column names are dropped.
Indexes that are created on any columns of the table are dropped.
All views based on the table are marked inoperative.
All privileges on the dropped table and dependent views are implicitly revoked.
All referential constraints in which the table is a parent or dependent are dropped.
All packages and cached dynamic SQL and XQuery statements dependent on the dropped table are marked invalid, and remain so until the dependent objects are re-created. Packages dependent on any supertable above the subtable in the hierarchy that is being dropped are included.
Any reference columns for which the dropped table is defined as the scope of the reference become unscoped.
An alias definition on the table is not affected because an alias can be undefined.
All triggers dependent on the dropped table are marked inoperative.

Restrictions

An individual table cannot be dropped if it has a subtable.

Procedure

To drop a table, use a DROP TABLE statement.
The following statement drops the table that is called DEPARTMENT:
```
   DROP TABLE DEPARTMENT
```
To drop all the tables in a table hierarchy, use a DROP TABLE HIERARCHY statement.
The DROP TABLE HIERARCHY statement must name the root table of the hierarchy to be dropped.
For example:
```
   DROP TABLE HIERARCHY person
```

Results

Dropping a table hierarchy in comparison to dropping a specific table have some distinct differences:

DROP TABLE HIERARCHY does not activate deletion-triggers that would be activated by individual DROP TABLE statements. For example, dropping an individual subtable would activate deletion-triggers on its supertables.
DROP TABLE HIERARCHY does not make log entries for the individual rows of the dropped tables. Instead, the dropping of the hierarchy is logged as a single event.