DROP

The DROP statement drops an object. Objects that are directly or indirectly dependent on that object may also be dropped.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

To drop a table, view, index, alias, or package, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • The system authorities of *OBJOPR and *OBJEXIST on the object to be dropped
    • If the object is a table or view, the system authorities of *OBJOPR and *OBJEXIST on any views, indexes, and logical files that are dependent on that table or view
    • If the object is a system-period temporal table, the system authorities of *OBJOPR and *OBJEXIST on the associated history table
    • The USAGE privilege on the schema that contains the object to be dropped
  • Database administrator authority

To drop a schema, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • The system authorities of *OBJEXIST, *OBJOPR, *EXECUTE, and *READ on the library to be dropped.
    • The system authorities of *OBJOPR and *OBJEXIST on all objects in the schema and *OBJOPR and *OBJEXIST on any views, indexes and logical files that are dependent on tables and views in the schema.
    • Any additional authorities required to delete other object types that exist in the schema. For example, *OBJMGT to the data dictionary if the schema contains a data dictionary, and some system data authority to the journal receiver. For more information, see Security Reference.
  • Database administrator authority

To drop a user-defined type, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • The system authorities of *OBJOPR and *OBJEXIST on the type to be dropped
    • The USAGE privilege on the schema that contains the type to be dropped
    • The DELETE privilege on the SYSTYPES, SYSPARMS, and SYSROUTINES catalog tables, and
    • The USAGE privilege on schema QSYS2
  • Database administrator authority

To drop a global variable, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • The system authority of *OBJEXIST on the *SRVPGM object for the global variable to be dropped
    • The USAGE privilege on the schema that contains the global variable to be dropped
    • The DELETE privilege on the SYSVARIABLES catalog table, and
    • The USAGE privilege on schema QSYS2
  • Database administrator authority

To drop an XSR object, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • The system authorities of *OBJOPR and *OBJEXIST on the *SQLXSR object for the XSR object to be dropped
    • The USAGE privilege on the schema that contains the XSR object to be dropped
    • The DELETE privilege on the XSROBJECTS, XSROBJECTCOMPONENTS, and XSRANNOTATIONINFO catalog tables, and
    • The USAGE privilege on schema QSYS2
  • Database administrator authority

To drop a function, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • For SQL functions, the system authority *OBJEXIST on the service program object associated with the function, and
    • The DELETE privilege on the SYSFUNCS, SYSPARMS, and SYSROUTINEDEP catalog tables, and
    • The USAGE privilege on schema QSYS2
  • Database administrator authority

To drop a procedure, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • For SQL procedures, the system authority *OBJEXIST on the program object associated with the procedure, and
    • The DELETE privilege on the SYSPROCS, SYSPARMS, and SYSROUTINEDEP catalog tables, and
    • The USAGE privilege on schema QSYS2
  • Database administrator authority

To drop a sequence, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following system authorities:
    • The system authority *OBJEXIST on the data area associated with the sequence, and
    • The USAGE privilege on the schema that contains the sequence to be dropped
    • The DELETE privilege on the SYSSEQOBJECTS catalog table, and
    • The USAGE privilege on schema QSYS2, and
    • *USE to the Delete Data Area (DLTDTAARA) command
  • Database administrator authority

To drop a trigger, the privileges held by the authorization ID of the statement must include at least one of the following:

  • The following privileges:
    • The system authority *USE to the Remove Physical File Trigger (RMVPFTRG) command, and
    • For the subject table or view of the trigger:
      • The ALTER privilege to the subject table or view, and
      • The USAGE privilege on the schema containing the subject table or view,
    • If the trigger being dropped is an SQL trigger:
      • The system authority *OBJEXIST on the trigger program object, and
      • The USAGE privilege on the schema containing the trigger.
  • Database administrator authority
To drop a mask or permission:
To drop an object used by a mask or permission:

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.

Syntax

Read syntax diagramSkip visual syntax diagramDROP ALIASIF EXISTSalias-nameFUNCTIONROUTINEIF EXISTSfunction-nameparameter-listSPECIFICFUNCTIONROUTINEIF EXISTSspecific-nameRESTRICTINDEXIF EXISTSindex-nameMASKIF EXISTSmask-namePACKAGEIF EXISTSpackage-nameVERSIONversion-idPERMISSIONIF EXISTSpermission-namePROCEDUREROUTINEIF EXISTSprocedure-nameparameter-listSPECIFICPROCEDUREROUTINEIF EXISTSspecific-nameRESTRICTSCHEMAIF EXISTSschema-nameRESTRICTCASCADESEQUENCEIF EXISTSsequence-nameRESTRICTTABLEIF EXISTStable-nameRESTRICTCASCADETRIGGERIF EXISTStrigger-nameTYPEIF EXISTSdistinct-type-namearray-type-nameRESTRICTCASCADEVARIABLEIF EXISTSvariable-nameRESTRICTCASCADEVIEWIF EXISTSview-nameRESTRICTCASCADEXSROBJECTIF EXISTSxsrobject-name
parameter-list
Read syntax diagramSkip visual syntax diagram( ,parameter-type )
parameter-type
Read syntax diagramSkip visual syntax diagramdata-typeAS LOCATOR
data-type
Read syntax diagramSkip visual syntax diagrambuilt-in-typedistinct-type-namearray-type-name
built-in-type
Read syntax diagramSkip visual syntax diagramSMALLINTINTEGERINTBIGINTDECIMALDECNUMERICNUM(5,0)(integer,0, integer)FLOAT(53)(integer)REALDOUBLEPRECISIONDECFLOAT(34)(16)CHARACTERCHAR(1)(integer)CHARACTERCHARVARYINGVARCHAR(integer)FOR BIT DATAFOR SBCS DATAFOR MIXED DATAccsid-clauseCHARACTERCHARLARGE OBJECTCLOB(1M)(integerKMG)FOR SBCS DATAFOR MIXED DATAccsid-clauseGRAPHIC(1)(integer)GRAPHIC VARYINGVARGRAPHIC(integer)DBCLOB(1M)(integerKMG)ccsid-clauseNATIONAL CHARACTERNATIONAL CHARNCHAR(1)(integer)NATIONAL CHARACTERNATIONAL CHARNCHARVARYINGNVARCHAR(integer)NATIONAL CHARACTERNCHARLARGE OBJECTNCLOB(1M)(integerKMG)BINARY(1)(integer)BINARY VARYINGVARBINARY(integer)BLOBBINARY LARGE OBJECT(1M)(integerKMG)DATETIME(0)TIMESTAMP(6)(integer)XMLccsid-clauseDATALINK(200)(integer)ccsid-clauseROWIDXML
ccsid-clause
Read syntax diagramSkip visual syntax diagramCCSIDinteger

Description

Start of changeIF EXISTSEnd of change
Start of changeSpecifies that no error is issued if the specified object does not exist. Unless other conditions or dependencies prevent the drop operation, a successful SQLSTATE is returned even if no object is dropped.End of change
ALIAS alias-name
Identifies the alias that is to be dropped. The alias-name must identify an alias that exists at the current server.

The specified alias is deleted from the schema. Dropping an alias has no effect on any constraint, view, or materialized query that was defined using the alias. An alias can be dropped whether it is referenced in a function, package, procedure, program, trigger, or variable.

FUNCTION or SPECIFIC FUNCTION
Identifies the function that is to be dropped. The function must exist at the current server and it must be a function that was defined with the CREATE FUNCTION statement. The particular function can be identified by its name, function signature, or specific name.

Functions implicitly generated by the CREATE TYPE statement cannot be dropped using the DROP statement. They are implicitly dropped when the distinct type is dropped.

The function cannot be dropped if another function is dependent on it. A function is dependent on another function if it was identified in the SOURCE clause of the CREATE FUNCTION statement. A function can be dropped whether it is referenced in a function, package, procedure, program, trigger, variable, or view unless RESTRICT is specified. A function cannot be dropped if it is referenced in a mask or permission even if RESTRICT is not specified.

The specified function is dropped from the schema. All privileges on the user-defined function are also dropped. If this is an SQL function or sourced function, the service program (*SRVPGM) associated with the function is also dropped. If this is an external function, the information that was saved in the program or service program specified on the CREATE FUNCTION statement is removed from the object.

FUNCTION function-name
Identifies the function by its name. The function-name must identify exactly one function. The function may have any number of parameters defined for it. If there is more than one function of the specified name in the specified or implicit schema, an error is returned.
FUNCTION function-name (parameter-type, ...)
Identifies the function by its function signature, which uniquely identifies the function. The function-name (parameter-type, ...) must identify a function with the specified function signature. The specified parameters must match the data types in the corresponding position that were specified when the function was created. The number of data types, and the logical concatenation of the data types is used to identify the specific function instance which is to be dropped. Synonyms for data types are considered a match. Parameters that have defaults must be included in this signature.

If function-name () is specified, the function identified must have zero parameters.

function-name
Identifies the name of the function.
(parameter-type, ...)
Identifies the parameters of the function.

If an unqualified distinct type name is specified, the database manager searches the SQL path to resolve the schema name for the distinct type.

For data types that have a length, precision, or scale attribute, use one of the following:

  • Empty parentheses indicate that the database manager ignores the attribute when determining whether the data types match. For example, DEC() will be considered a match for a parameter of a function defined with a data type of DEC(7,2). However, FLOAT cannot be specified with empty parenthesis because its parameter value indicates a specific data type (REAL or DOUBLE).
  • If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement. If the data type is FLOAT, the precision does not have to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
  • If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE FUNCTION statement.

Specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If either clause is specified, it must match the value that was implicitly or explicitly specified in the CREATE FUNCTION statement.

AS LOCATOR
Specifies that the function is defined to receive a locator for this parameter. If AS LOCATOR is specified, the data type must be a LOB or XML or a distinct type based on a LOB or XML.
SPECIFIC FUNCTION specific-name
Identifies the function by its specific name. The specific-name must identify a specific function that exists at the current server.
RESTRICT
Specifies that the function cannot be dropped if it is referenced in an SQL function, SQL procedure, table, mask, permission, SQL trigger, variable, or view.
RESTRICT not specified
If the function is referenced in a mask or permission, the drop will fail unless the authorization ID of the statement has security administrator authority.
INDEX index-name
Identifies the index that is to be dropped. The index-name must identify an index that exists at the current server.

The specified index is dropped from the schema. An index can be dropped whether it is referenced in a function, package, procedure, program, or trigger.

MASK mask-name
Identifies the mask that is to be dropped. The mask-name must identify a mask that exists at the current server.

The specified mask is dropped from the schema. A mask can be dropped whether it is referenced in a function, package, procedure, program, or trigger.

PACKAGE package-name
Identifies the package that is to be dropped. The package-name must identify a package that exists at the current server.

The specified package is dropped from the schema. All privileges on the package are also dropped.

A package can be dropped whether it is referenced in a function, package, procedure, program, or trigger.

VERSION version-id
version-id is the version identifier that was assigned to the package when it was created. If version-id is not specified, a null string is used as the version identifier.
PERMISSION permission-name
Identifies the permission that is to be dropped. The permission-name must identify a permission that exists at the current server.

The specified permission is dropped from the schema. A permission can be dropped whether it is referenced in a function, package, procedure, program, or trigger.

PROCEDURE or SPECIFIC PROCEDURE
Identifies the procedure that is to be dropped. The procedure-name must identify a procedure that exists at the current server.

The specified procedure is dropped from the schema. All privileges on the procedure are also dropped. If this is an SQL procedure, the program (*PGM) or service program (*SRVPGM) associated with the procedure is also dropped. If this is an external procedure, the information that was saved in the program specified on the CREATE PROCEDURE statement is removed from the object.

A procedure can be dropped whether it is referenced in a function, package, procedure, program, or trigger.

PROCEDURE procedure-name
Identifies the procedure by its name. The procedure-name must identify exactly one procedure. The procedure may have any number of parameters defined for it. If there is more than one procedure of the specified name in the specified or implicit schema, an error is returned.
PROCEDURE procedure-name (parameter-type, ...)
Identifies the procedure by its procedure signature, which uniquely identifies the procedure. The procedure-name (parameter-type, ...) must identify a procedure with the specified procedure signature. The specified parameters must match the data types in the corresponding position that were specified when the procedure was created. The number of data types, and the logical concatenation of the data types is used to identify the specific procedure instance which is to be dropped. Synonyms for data types are considered a match. Parameters that have defaults must be included in this signature.

If procedure-name () is specified, the procedure identified must have zero parameters.

procedure-name
Identifies the name of the procedure.
(parameter-type, ...)
Identifies the parameters of the procedure.

If an unqualified distinct type or array type name is specified, the database manager searches the SQL path to resolve the schema name for the distinct type or array type.

For data types that have a length, precision, or scale attribute, use one of the following:

  • Empty parentheses indicate that the database manager ignores the attribute when determining whether the data types match. For example, DEC() will be considered a match for a parameter of a procedure defined with a data type of DEC(7,2). However, FLOAT cannot be specified with empty parenthesis because its parameter value indicates a specific data type (REAL or DOUBLE).
  • If a specific value for a length, precision, or scale attribute is specified, the value must exactly match the value that was specified (implicitly or explicitly) in the CREATE PROCEDURE statement. If the data type is FLOAT, the precision does not have to exactly match the value that was specified because matching is based on the data type (REAL or DOUBLE).
  • If length, precision, or scale is not explicitly specified, and empty parentheses are not specified, the default attributes of the data type are implied. The implicit length must exactly match the value that was specified (implicitly or explicitly) in the CREATE PROCEDURE statement.

Specifying the FOR DATA clause or CCSID clause is optional. Omission of either clause indicates that the database manager ignores the attribute when determining whether the data types match. If either clause is specified, it must match the value that was implicitly or explicitly specified in the CREATE PROCEDURE statement.

AS LOCATOR
Specifies that the procedure is defined to receive a locator for this parameter. If AS LOCATOR is specified, the data type must be a LOB or XML or a distinct type based on a LOB or XML.
SPECIFIC PROCEDURE specific-name
Identifies the procedure by its specific name. The specific-name must identify a specific procedure that exists at the current server.
RESTRICT
Specifies that the procedure cannot be dropped if it is referenced in an SQL function, SQL procedure, or SQL trigger.
SCHEMA schema-name
Identifies the schema that is to be dropped. The schema-name must identify a schema that exists at the current server.

The specified schema is dropped. Each object in the schema is dropped as if the appropriate DROP statement was executed with the specified drop option (CASCADE, RESTRICT, or neither). See the DROP description of these object types for information about the handling of objects dependent on these objects.

DROP SCHEMA is only valid when the commit level is *NONE.

Neither CASCADE nor RESTRICT
Specifies that the schema will be dropped even if it is referenced in a function, package, procedure, program, table, mask, permission, trigger, or variable in another schema.
CASCADE
Specifies that any objects in the schema and any triggers that reference the schema will be dropped. Any masks and permissions in a different schema that reference the schema are dropped if the authorization ID of the statement has security administrator authority.
RESTRICT
Specifies that the schema cannot be dropped if it is referenced in an SQL trigger, mask, or permission in another schema or if the schema contains any SQL objects other than catalog views, the journal, and journal receiver.
SEQUENCE sequence-name
Identifies the sequence that is to be dropped. The sequence-name must identify a sequence that exists at the current server.
RESTRICT
Specifies that the sequence cannot be dropped if it is referenced in an SQL trigger, function, procedure, or variable.
TABLE table-name
Identifies the table that is to be dropped. The table-name must identify a base table that exists at the current server, but must not identify a catalog table or a history table for a system-period temporal table.

The specified table is dropped from the schema. All privileges, constraints, indexes, masks, permissions, and triggers on the table are also dropped.

Any aliases that reference the specified table are not dropped.

Neither CASCADE nor RESTRICT
Specifies that the table will be dropped even if it is referenced in a constraint, index, trigger, mask, permission, view, or materialized query table. All indexes, views, and materialized query tables that reference the table are dropped even if the authorization ID of the statement does not explicitly have privileges to those objects. If the table is a system-period temporal table, the history table is also dropped. If the table is referenced in a mask or permission, the drop will fail unless the authorization ID of the statement has security administrator authority.
CASCADE
Specifies that the table will be dropped even if it is referenced in a constraint, index, trigger, variable, mask, permission, view, XSR object, or materialized query table. All constraints, indexes, triggers, variables, views, XSR objects and materialized query tables that reference the table are dropped even if the authorization ID of the statement does not explicitly have privileges to those objects. If the table is a system-period temporal table, the history table is also dropped. All masks and permissions that reference the table are dropped if the authorization ID of the statement has security administrator authority.
RESTRICT
Specifies that the table cannot be dropped if it is referenced in a constraint, index, mask, permission, trigger, variable, view, XSR object, or materialized query table, or if it is a system-period temporal table.
TRIGGER trigger-name
Identifies the trigger that is to be dropped. The trigger-name must identify a trigger that exists at the current server.

The specified trigger is dropped from the schema. If the trigger is an SQL trigger, the program object associated with the trigger is also deleted from the schema.

If trigger-name specifies an INSTEAD OF trigger on a view, another trigger may depend on that trigger through an update against the view.

TYPE distinct-type-name or array-type-name
Identifies the type that is to be dropped. The distinct-type-name or array-type-name must identify a distinct type or array type that exists at the current server. The specified type is deleted from the schema.
Neither CASCADE nor RESTRICT
Specifies that the type cannot be dropped if any constraints, indexes, masks, permissions, sequences, tables, variables, and views reference the type.

For every procedure or function R that has parameters or a return value of the type being dropped, the following DROP statement is effectively executed:

   DROP ROUTINE R

For every trigger T that references the type being dropped, the following DROP statement is effectively executed:

   DROP TRIGGER T

It is possible that this statement would cascade to drop dependent functions or procedures. If all of these functions or procedures are in the list to be dropped because of a dependency on the type, the drop of the type will succeed.

CASCADE
Specifies that the type will be dropped even if it is referenced in a constraint, function, index, procedure, sequence, table, trigger, variable, or view. All constraints, functions, indexes, procedures, sequences, tables, triggers, variables, and views that reference the type are dropped. The cascade processing is limited to cases where the type is used to define the routine parameter type, sequence type, variable type and column type. All masks and permissions that reference the type are dropped if the authorization ID of the statement has security administrator authority.
RESTRICT
Specifies that the type cannot be dropped if it is referenced in a constraint, function (other than a function that was created when the type was created), index, procedure, sequence, table, mask, permission, trigger, variable, or view. The restrict checking is limited to cases where the type is used to define the routine parameter type, sequence type, variable type and column type.
VARIABLE variable-name
Identifies the variable that is to be dropped. The variable-name must identify a variable that exists at the current server. The specified variable is dropped from the schema.
Neither CASCADE nor RESTRICT
Specifies that the variable will be dropped even if it is referenced in a trigger, procedure, function, mask, permission, view, or another variable. All tables and views that reference the variable are dropped. If the variable is referenced in a mask or permissions, the drop will fail unless the authorization ID of the statement has security administrator authority.
CASCADE
Specifies that the variable will be dropped even if it is referenced in a table, trigger, procedure, function, view, or another variable. All tables, triggers, procedures, functions, views, and variables that reference the variable are dropped. All masks and permissions that reference the variable are dropped if the authorization ID of the statement has security administrator authority.
RESTRICT
Specifies that the variable cannot be dropped if it is referenced in a table, trigger, procedure, function, mask, permission, view, or another variable.
VIEW view-name
Identifies the view that is to be dropped. The view-name must identify a view that exists at the current server, but must not identify a catalog view.

The specified view is dropped from the schema. When a view is dropped, all privileges and triggers on that view are dropped.

Neither CASCADE nor RESTRICT
Specifies that the view will be dropped even if it is referenced in a trigger, materialized query table, mask, permission, or another view. All views and materialized query tables that reference the view are dropped. If the view is referenced in a mask or permissions, the drop will fail unless the authorization ID of the statement has security administrator authority.
CASCADE
Specifies that the view will be dropped even if it is referenced in a trigger, variable, materialized query table, or another view. All triggers, variables, materialized query tables, and views that reference the view are dropped. All masks and permissions that reference the view are dropped if the authorization ID of the statement has security administrator authority.
RESTRICT
Specifies that the view cannot be dropped if it is referenced in a trigger, variable, mask, permission, materialized query table, or another view.
XSROBJECT xsrobject-name
Identifies the XSR object that is to be dropped. The xsrobject-name must identify an XSR object that exists at the current server. The specified XSR object is dropped.

Notes

Drop effects: Whenever an object is dropped, its description is dropped from the catalog. If the object is referenced in a function, package, procedure, program, trigger, or variable; any access plans that reference the object are implicitly prepared again when the access plan is next used. If the object does not exist at that time, an error is returned.

Dependencies: Whenever an object is directly or indirectly dropped, other objects that depend on the dropped object might also be dropped. The following semantics determine what happens to a dependent object when the object that it depends on (the underlying object) is dropped:

Three different types of dependencies are shown:
D
Dependent object is dropped.
A
Automatic revalidation is required. The database manager will attempt to revalidate the object when it is referenced.
R
DROP statement fails.
Table 1. Effect of dropping objects that have dependencies
 Drop Statement
ALIAS
CONSTRAINT
FUNCTION
INDEX
MASK
PERMISSION
PROCEDURE
SCHEMA
SEQUENCE
TABLE
TRIGGER
TYPE
VARIABLE
VIEW
DROP ALIAS                            
DROP FUNCTION     R1   A A A     A A   A A
DROP FUNCTION RESTRICT     R   R R R     R R   R R
DROP INDEX                            
DROP MASK                            
DROP PACKAGE                            
DROP PERMISSION                            
DROP PROCEDURE     A       A       A      
DROP PROCEDURE RESTRICT     R       R       R      
DROP SCHEMA2     A   A A A     A A   A A
DROP SCHEMA CASCADE3     A   D D A     A D   A A
DROP SCHEMA RESTRICT4     A   R R A     A R   A A
DROP SEQUENCE     A       A       A   A  
DROP SEQUENCE RESTRICT     R       R       R   R  
DROP TABLE   D A D A A A     D A   A D
DROP TABLE CASCADE   D A D D D A     D D   D D
DROP TABLE RESTRICT   R A R R R A     R R   R R
DROP TRIGGER                            
DROP TYPE   R D5 R R R D   R R D   R R
DROP TYPE CASCADE   D D D D D D   D D D   D D
DROP TYPE RESTRICT   R R R R R R   R R R   R R
DROP VARIABLE     A   A A A     D A   A D
DROP VARIABLE CASCADE     D   D D D     D D   D D
DROP VARIABLE RESTRICT     R   R R R     R R   R R
DROP VIEW     A   A A       D A   A D
DROP VIEW CASCADE     A   D D A     D D   D D
DROP VIEW RESTRICT     A   R R A     R R   R R
DROP XSROBJ     A   A A A       A   A A
Notes:
  1. If other user-defined functions are sourced on the function being dropped, the function cannot be dropped.
  2. DROP SCHEMA will drop all objects in the schema. Any objects in other schemas that reference the schema will not be changed and will require automatic revalidation.
  3. DROP SCHEMA CASCADE will drop all objects in the schema. Triggers in other schemas that reference the schema will be dropped. Other objects in other schemas that reference the schema will not be changed and will require automatic revalidation.
  4. DROP SCHEMA RESTRICT will fail if any object other than SQL catalog views, journals, and journal receivers exist in the schema. Objects other than triggers in other schemas that reference the schema will not be changed and will require automatic revalidation.
  5. Any function or procedure that has a parameter or returns value defined as the type will be dropped.

Syntax alternatives: The following keywords are synonyms supported for compatibility to prior releases. These keywords are non-standard and should not be used:

  • The keyword SYNONYM can be used as a synonym for ALIAS.
  • The keywords DATA TYPE or DISTINCT TYPE can be used as a synonym for TYPE.
  • The keyword PROGRAM can be used as a synonym for PACKAGE.
  • The keyword COLLECTION can be used as a synonym for SCHEMA.

Start of changeFor compatibility with other database products, the IF EXISTS clause can be specified following table-name for DROP TABLE.End of change

Examples

Example 1: Drop your table named MY_IN_TRAY. Do not allow the drop if any views or indexes are created over this table.

   DROP TABLE MY_IN_TRAY RESTRICT

Example 2: Drop your view named MA_PROJ.

   DROP VIEW MA_PROJ

Example 3: Drop the package named PERS.PACKA.

   DROP PACKAGE PERS.PACKA

Example 4: Drop the distinct type DOCUMENT, if it is not currently in use:

   DROP DISTINCT TYPE DOCUMENT RESTRICT

Example 5: Assume that you are SMITH and that ATOMIC_WEIGHT is the only function with that name in schema CHEM. Drop ATOMIC_WEIGHT.

   DROP FUNCTION CHEM.ATOMIC_WEIGHT RESTRICT

Example 6: Drop the function named CENTER, using the function signature to identify the function instance to be dropped.

   DROP FUNCTION CENTER (INTEGER, FLOAT) RESTRICT

Example 7: Drop CENTER, using the specific name to identify the function instance to be dropped.

   DROP SPECIFIC FUNCTION JOHNSON.FOCUS97

Example 8: Assume that procedure OSMOSIS is in schema BIOLOGY. Drop OSMOSIS.

   DROP PROCEDURE BIOLOGY.OSMOSIS

Example 9: Assume that trigger BONUS exists in the default schema. Drop BONUS.

   DROP TRIGGER BONUS