|
Removes a table from the database.
DROP TABLE [ IF EXISTS ] [ owner.]table-name
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.
Global temporary tables cannot be dropped unless all users that have referenced the temporary table have disconnected.
You must be the owner of the table, or have the DROP ANY TABLE or DROP ANY OBJECT system privilege.
Automatic commit. DROP TABLE may also cause an automatic checkpoint. 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.
Core Feature, however, the IF EXISTS clause is not in the standard. The ability to drop a declared local temporary table with the DROP TABLE statement is not in the standard.
This example drops the fictitious MyTable table from the database.
DROP TABLE MyTable;
This example drops the fictitious MyTable table from the database. Because IF EXISTS is specified, if the table does not exist, an error is not returned.
DROP TABLE IF EXISTS MyTable;