DROP_SCHEMA procedure

Drops ASCHEMA and all objects within it.

Prototypes

DROP_SCHEMA(ASCHEMA VARCHAR(128))

Description

DROP_SCHEMA is a utility procedure which drops all objects (tables, views, triggers, sequences, aliases, etc.) in a schema and then drops the schema. It was originally intended to make destruction of user-owned schemas easier (in the event that a user no longer required access) but can also be used to make writing upgrade scripts easier.

Note

This procedure is effectively redundant since DB2 9.5 which includes the built-in procedure ADMIN_DROP_SCHEMA, albeit with a somewhat more complicated calling convention.

Parameters

ASCHEMA
The name of the schema to drop.

Examples

Drop the FRED schema and all objects within it:

CALL DROP_SCHEMA('FRED');

Drop all schemas which start with the characters TEST:

BEGIN ATOMIC
  FOR T AS
    SELECT SCHEMANAME
    FROM SYSCAT.SCHEMATA
    WHERE SCHEMANAME LIKE 'TEST%'
  DO
    CALL DROP_SCHEMA(T.SCHEMANAME);
  END FOR;
END!

See Also