Modifying Schema, View, Table, and Routine Definitions › Maintaining Tables › Dropping a Table

Dropping a Table

DROP TABLE Statement

To drop a table, use the SQL DDL DROP TABLE statement. Use the CASCADE option if the table participates in a referential constraint or is referenced in one or more view definitions.

No CASCADE

When you drop a table (without CASCADE), the following definitions are removed from the dictionary:

• The table
• Its CALC key (if any)
• All indexes defined on the table
• All privileges granted on the table

Table rows and indexes are removed from the database.

With CASCADE

If you specify CASCADE, these additional definitions are removed from the dictionary:

• All referential constraints in which the table participates
• All views in which the table is referenced and all views referencing those views
• All privileges granted on views dropped as a result of cascade processing

Considerations

Using FORMAT to Erase Table Rows

If the table you want to drop is the only table in an area, it participates in no linked constraints and its indexes (if any) also reside in areas in which no other table or index resides, you can use the FORMAT utility to drop the table more efficiently:

1. Format the area(s) containing the table and indexes
2. Drop the table

Note: For more information about FORMAT, see the CA IDMS Utilities Guide.

Dropping All Tables in a Schema

If you want to drop all tables in a schema, use the DROP SCHEMA statement with the CASCADE option rather than dropping each table individually.

Example

In the following example, these entities are dropped: the BENEFITS table, its CALC key, all indexes defined on it, all privileges on it, all referential constraints in which BENEFITS participates, all views in which this table is referenced and all views referencing that view, and all privileges granted on all those views. In addition, all data will be deleted.

drop table demoempl.benefits cascade;