DROP SCHEMA

The DROP SCHEMA data description statement deletes a schema definition from the dictionary.

Authorization

To issue a DROP SCHEMA statement, you must have the DROP privilege on the schema named in the statement.

Note: You need no additional privileges to issue a DROP SCHEMA statement with the CASCADE parameter.

Syntax

►►─── DROP SCHEMA schema-name ──┬───────────┬─────────────────────────────────►◄
                                └─ CASCADE ─┘

Parameters

schema-name

Specifies the name of the schema being dropped. Schema-name must identify a schema defined in the dictionary.

CASCADE

Directs CA IDMS to perform a DROP TABLE CASCADE, DROP VIEW CASCADE, DROP PROCEDURE CASCADE , DROP TABLE PROCEDURE CASCADE, or a DROP FUNCTION CASCADE for each table, view, procedure, table procedure and function associated with the named schema.

If you do not specify CASCADE in a DROP SCHEMA statement, the schema named in the statement cannot have any associated tables, views, functions, procedures and table procedures.

Usage

SYSTEM Schema

You cannot drop the SYSTEM schema.

Effect of the CASCADE Parameter

When you specify CASCADE in a DROP SCHEMA statement, CA IDMS deletes the following:

The definition of each table, view, function, procedure and table procedure associated with the named schema
The data stored in each table associated with the schema
The definition of each referential constraint, index, and CALC key defined on the tables associated with the named schema
The view definition of each view derived from one or more of the tables, views, functions, procedures or table procedures associated with the named schema
For functions and procedures with language SQL, the statement removes the SQL routine body from the dictionary and the associated CA ADS entities and program structures: access module(AM), relational command module (RCM), ADS premap process code and dialog load module

Linked Constraints with Non-empty Tables in Other Schemas

If any tables in the schema to be dropped participate in linked referential constraints with non-empty tables in other schemas, CA IDMS also updates rows of those tables to remove the physical links with the tables being deleted.

Example

Dropping an Empty Schema

The following DROP SCHEMA statement deletes the definition of the SALES schema from the dictionary only if the schema has no associated tables, views, functions, procedures or table procedures:

drop schema sales;

Note: For more information about defining schemas, see ALTER SCHEMA and CREATE SCHEMA.