Use this statement to remove objects from the database.

Syntax

DROP
 { DOMAIN | DATATYPE } datatype-name
| DBSPACE dbspace-name
| EVENT [ owner.]event-name
| FUNCTION [ owner.]function-name
| INDEX { [ [owner.]table-name.]index-name | [ [owner.]materialized-view-name.]index-name }
| MATERIALIZED VIEW [ owner.]materialized-view-name
| MESSAGE msgnum
| PROCEDURE [ owner.]procedure-name
| TABLE [ owner.]table-name
| TRIGGER [ [ owner.]table-name.]trigger-name
| VIEW [ owner.]view-name

Remarks

The DROP statement removes the definition of the indicated database object. If the object is a dbspace, all tables in that dbspace must be dropped prior to dropping the dbspace. If the object is a table or materialized view, all data in the table is automatically deleted as part of the dropping process. Also, all indexes and keys for a table or materialized view are dropped as well. You cannot use the DROP DBSPACE statement to drop the pre-defined dbspaces system, temporary, temp, translog, or translogmirror. See Pre-defined dbspaces.

DROP TABLE, DROP MATERIALIZED VIEW, DROP INDEX, DROP DBSPACE, DROP PROCEDURE and DROP FUNCTION are prevented whenever the statement affects an object that is currently being used by another connection. DROP TABLE is prevented if there is a materialized view dependent on the table.

DROP TABLE, DROP MATERIALIZED VIEW, and DROP VIEW cause the status of all dependent regular views to become INVALID. To determine view dependencies before dropping a table, view or materialized view, use the sa_dependent_views system procedure. See sa_dependent_views system procedure.

DROP DOMAIN is prevented if the data type is used in a table column, or in a procedure or function argument. You must change data types on all columns defined using the domain in order to drop the data type. It is recommended that you use DROP DOMAIN rather than DROP DATATYPE, as DROP DOMAIN is the syntax used in the ANSI/ISO SQL3 draft. You cannot drop system-defined data types (such as MONEY or UNIQUEIDENTIFIERSTR) from a database.

Permissions

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

For DROP DBSPACE, you must be the only connection to the database.

A user with ALTER permissions on the table can execute DROP TRIGGER.

A user with REFERENCES permissions on the table can execute DROP INDEX.

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

The DROP INDEX statement cannot be executed when there are cursors opened with the WITH HOLD clause that use either statement or transaction snapshots. See Snapshot isolation.

Side effects

Automatic commit. Clears the Results tab in the Results pane in Interactive SQL. DROP TABLE, DROP VIEW, DROP MATERIALIZED VIEW, and DROP INDEX close all cursors for the current connection.

DROP TABLE can be used to drop a local temporary table, but DROP INDEX cannot be used to drop an index on a local temporary table. An attempt to do so results in an Index not found error. Indexes on local temporary tables are dropped automatically when the local temporary table goes out of scope.

When a view is dropped, all procedures and triggers are unloaded from memory, so that any procedure or trigger that references the view reflects the fact that the view does not exist. The unloading and loading of procedures and triggers can have a performance impact if you are regularly dropping and creating views.

See also

• CREATE DATABASE statement
• CREATE DOMAIN statement
• CREATE INDEX statement
• CREATE FUNCTION statement
• CREATE PROCEDURE statement
• CREATE TABLE statement
• CREATE TRIGGER statement
• CREATE VIEW statement
• CREATE STATISTICS statement
• CREATE MATERIALIZED VIEW statement

Standards and compatibility

Example

Drop a fictitious table, MyTable, from the database.

DROP TABLE MyTable;

Drop the prices index from a fictitious table called ProductIDsPerCustomer.

DROP INDEX ProductIDsPerCustomer.prices;