Reference Manual
   CHAPTER 9. SQL Statements     

DROP statement

Function 

To remove objects from the database.

Syntax 

DROP
DATABASE file-name
| { DATATYPE | DOMAIN } datatype-name
| DBSPACE dbspace-name
| FUNCTION [ owner.]function-name
| INDEX [[owner].table-name.]index-name
| PROCEDURE [ owner.]procedure-name
| TABLE [ owner.]table-name
| TRIGGER [[ owner.]table-name.]trigger-name
| VIEW [ owner.]view-name

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.

Side effects 

Automatic commit. Clears the Data window in Interactive SQL. DROP TABLE and DROP INDEX close all cursors for the current connection.

Local temporary tables is an exception; no commit is performed when one is dropped.

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

Description 

The DROP statement removes the definition of the indicated database structure. If the structure is a dbspace, all tables in that dbspace must be dropped prior to dropping the dbspace. If the structure is a table, all data in the table is automatically deleted as part of the dropping process. Also, all indexes and keys for the table are dropped by the DROP TABLE statement.

DROP TABLE, DROP INDEX, and DROP DBSPACE are prevented whenever the statement affects a table that is currently being used by another connection.

DROP PROCEDURE and DROP FUNCTION are prevented when the procedure or function is in use by another connection.

DROP DATATYPE is prevented if the data type is used in a table. You must change data types on all columns defined on the user-defined data type 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.

Standards and compatibility 

• SQL/92     Entry level feature.

• Sybase     Supported by Adaptive Server Enterprise for those objects that exist in Adaptive Server Enterprise.

Examples 

• Drop the department table from the database.

  DROP TABLE department

• Drop the emp_dept view from the database.

  DROP VIEW emp_dept