User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To remove objects from the database.

Syntax

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

Usage

Anywhere.

Permissions

For DROP DBSPACE, must have DBA authority and must be the only connection to the database.

For DROP TRIGGER, must have ALTER permissions on the table.

For others, must be the owner of the object, or have DBA authority.

Side effects

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

See also

• CREATE DATATYPE statement
• CREATE DBSPACE statement
• CREATE TABLE statement
• CREATE INDEX statement
• CREATE VIEW statement
• CREATE PROCEDURE statement
• CREATE FUNCTION statement
• CREATE TRIGGER statement
• ALTER TABLE statement

Description

The DROP statement removes the definition of the indicated database structure. If the structure is a dbspace, then 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 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.

Examples

Drop the department table from the database.

     DROP TABLE department

Drop the emp_dept view from the database.

     DROP VIEW emp_dept