Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Reference » SQL Statements

DISCONNECT statement [ESQL] [Interactive SQL] Next Page

DROP statement


Use this statement to remove objects from the database.

Syntax

DROP
{ DOMAIN | DATATYPE } datatype-name
| DBSPACE dbspace-name
| EVENT event-name
| FUNCTION [ owner.]function-name
| INDEX { [ [owner.]table-name.]index-name | [ [owner.]materialized-view-name.]index-name }
| MESSAGE msgnum
| PROCEDURE [ owner.]procedure-name
| TABLE [ owner.]table-name
| TRIGGER [ [ owner.]table-name.]trigger-name
| VIEW [ owner.]view-name
| MATERIALIZED VIEW [ owner.]materialized-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 non-materialized 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 used within a snapshot transaction when snapshot isolation is enabled for the database. 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
Standards and compatibility
Example

Drop the Departments table from the database.

DROP TABLE Departments;

Drop the EmployeesAndDepartments view from the database.

DROP VIEW EmployeesAndDepartments;

Drop the price index from the ProductIDsPerCustomer materialized view.

DROP INDEX ProductIDsPerCustomer.price;