Use this statement to remove a table from the database.

Syntax

DROP TABLE [ IF EXISTS ] [ owner.]table-name 

Remarks

When you remove a table, all data in the table is automatically deleted as part of the dropping process. All indexes and keys for the table are dropped as well.

Use the IF EXISTS clause if you do not want an error returned when the DROP TABLE statement attempts to remove a table that does not exist.

You cannot execute a DROP TABLE statement when the statement affects a table that is currently being used by another connection. Execution of a DROP TABLE statement is also prevented if there is a materialized view dependent on the table.

When you execute a DROP TABLE statement, the status of all dependent regular views change to INVALID. To determine view dependencies before dropping a table, use the sa_dependent_views system procedure. See sa_dependent_views system procedure.

Permissions

Any user who owns the object, or has DBA authority, can execute the DROP TABLE statement.

Global temporary tables cannot be dropped unless all users that have referenced the temporary table have disconnected.

Side effects

Automatic commit. Clears the Results tab in the Results pane in Interactive SQL. Executing a DROP TABLE statement closes all cursors for the current connection.

You can use the DROP TABLE statement to drop a local temporary table.

See also

• CREATE TABLE statement
• ALTER TABLE statement

Standards and compatibility

Example

Drop MyTable from the database. If the table does not exist, an error is returned.

DROP TABLE MyTable;

Drop MyTable from the database if it exists. If the table does not exist, an error is not returned.

DROP TABLE IF EXISTS MyTable;