Skip to main content

DROP SCHEMA (SQL)

Deletes the schema definition.

Synopsis

DROP SCHEMA [ IF EXISTS ] name [ CASCADE | RESTRICT ]

Arguments

Argument Description
name The name of the schema to be dropped. The name is an identifier.
IF EXISTS Optional — Suppresses the error that arises if a schema with name does not exist.
CASCADE Optional — Specifies that all objects with a schema are dropped, including tables, views, queries and methods projected as stored procedures, and user-defined aggregates.
RESTRICT Optional — Specifies that the schema should only be dropped if nothing is defined within it. This option is assumed if CASCADE has not been specified.

Description

This command deletes a schema definition. The user that issues the command must either own the schema or have the %SQLSchemaAdmin resource in order to execute the operation.

If CASCADE is specified, all tables, views, queries and methods projected as stored procedures, and user-defined aggregates within the schema are dropped.

By default, the RESTRICT option is specified, but you may also specify it manually. When it is specified, the schema will only be dropped if nothing is defined within it. If DROP SCHEMA is specified without CASCADE and the schema is not empty, SQLCODE -475 is returned.

DROP SCHEMA provides an implicit %NOJOURN to suppress journaling and disable transactions while the operation is running. It also provides an implicit %DELDATA to delete data associated with the tables it drops when CASCADE has been specified.

If you run DROP SCHEMA on a schema that does not exist, SQLCODE -473 is returned.

See Also

Next sectionDROP TABLE (SQL)
Previous sectionDROP ROLE (SQL)
FeedbackOpens in a new tab