ASA SQL Reference
  SQL Statements

DROP statement

Description 

Use this statement to remove objects from the database.

Syntax 

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

Usage 

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 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.

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 Results tab in the Results pane 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.

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

Standards and compatibility 

• SQL/92    Entry-level feature.

• SQL/99    Core feature.

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

Example 

• Drop the department table from the database.

DROP TABLE department

Drop the emp_dept view from the database.

DROP VIEW emp_dept