Deletes a schema and all the definitions that it contains.

1  –  Environment

    You can use the DROP SCHEMA statement:

    o  In interactive SQL

    o  Embedded in host language programs to be precompiled

    o  As part of a procedure in an SQL module

    o  In dynamic SQL as a statement to be dynamically executed

2  –  Format

  DROP SCHEMA <schema-name> -+-+---------------+-+-->
                             | +-> CASCADE  ---+ |
                             | +-> RESTRICT  --+ |
                             | +-> IF EXISTS --+ |
                             +----------<--------+

3  –  Arguments

3.1  –  CASCADE

    Deletes all other definitions (views, constraints, tables,
    sequences, indexes, and triggers) that refer to the named schema
    and then deletes that schema definition. This is known as a
    cascading delete.

    If you specify the CASCADE keyword, SQL deletes all definitions
    contained by the schema before deleting the schema.

    If you do not specify the CASCADE keyword, the schema must be
    empty.

3.2  –  IF_EXISTS

    Prevents SQL command language from displaying error messages if
    the referenced object does not exist in the database.

3.3  –  RESTRICT

    Returns an error message if other definitions refer to the named
    schema. The DROP SCHEMA RESTRICT statement will not delete a
    schema until you have deleted all other definitions that refer to
    the named schema. The DROP SCHEMA statement specifies an implicit
    RESTRICT by default.

3.4  –  schema-name

    Specifies the schema name. You must qualify the schema name with
    catalog and alias names if the schema is not in the default
    catalog and database. For more information about schema names,
    see the User_Supplied_Names HELP topic.

4  –  Examples

    Example 1: Deleting a schema with implicit RESTRICT

    In the following example, the user must delete the definitions
    that refer to the schema RECRUITING before deleting the schema
    itself.

    After setting the default schema to RECRUITING and the default
    catalog to ADMINISTRATION, the user can qualify each definition
    name with only the alias CORP.

    SQL> ATTACH 'ALIAS CORP FILENAME CORPORATE_DATA';
    SQL> SET CATALOG '"CORP.ADMINISTRATION"';
    SQL> SET SCHEMA '"CORP.ADMINISTRATION".RECRUITING';
    SQL> SET QUOTING RULES 'SQL92';
    SQL> DROP SCHEMA "CORP.RECRUITING";
    %RDB-E-NO_META_UPDATE, metadata update failed
    -RDMS-E-SCHEMAINUSE, schema RECRUITING currently in use
    SQL> DROP TABLE "CORP.CANDIDATES";
    SQL> DROP TABLE "CORP.COLLEGES";
    %RDB-E-NO_META_UPDATE, metadata update failed
    -RDMS-F-CONEXI, relation COLLEGES is referenced in constraint DEGREES_FOREIGN3
    -RDMS-F-RELNOTDEL, relation COLLEGES has not been deleted
    SQL> DROP TABLE "CORP.DEGREES";
    %RDB-E-NO_META_UPDATE, metadata update failed
    -RDMS-F-TRGEXI, relation DEGREES is referenced in trigger
    EMPLOYEE_ID_CASCADE_DELETE
    SQL> DROP TABLE "CORP.RESUMES";
    %RDB-E-NO_META_UPDATE, metadata update failed
    -RDMS-F-TRGEXI, relation RESUMES is referenced in trigger
    EMPLOYEE_ID_CASCADE_DELETE
    -RDMS-F-RELNOTDEL, relation RESUMES has not been deleted
    SQL> --
    SQL> -- The trigger is part of another schema, PERSONNEL. Since this
    SQL> -- is not the default schema, the user qualifies the trigger name
    SQL> -- with schema and names.
    SQL> --
    SQL> DROP TRIGGER "CORP.ADMINSTRATION".PERSONNEL.EMPLOYEE_ID_CASCADE_DELETE;
    SQL> DROP CONSTRAINT "CORP.DEGREES_FOREIGN3";
    %RDB-E-NO_META_UPDATE, metadata update failed
    -RDMS-F-CONDELVIAREL, constraint DEGREES_FOREIGN3 can only be deleted by
    changing or deleting relation DEGREES
    SQL> DROP TABLE "CORP.DEGREES";
    SQL> DROP TABLE "CORP.RESUMES";
    SQL> DROP TABLE "CORP.COLLEGES";
    SQL> DROP SCHEMA "CORP.RECRUITING";

    Example 2: Deleting a schema with CASCADE

    In the following example, SQL deletes the definitions that refer
    to the schema ACCOUNTING, then deletes the schema itself:

    SQL> DROP SCHEMA "CORP.ACCOUNTING" CASCADE;
    Domain "CORP.ADMINISTRATION".ACCOUNTING.BUDGET is also being dropped.
    Domain "CORP.ADMINISTRATION".ACCOUNTING.CODE is also being dropped.
    SQL>