DROP SCHEMA

Permanently removes a schema from the database.

Permanently removes a schema from the database. Be sure that you want to remove the schema before you drop it, because DROP SCHEMA is an irreversible process. Use the CASCADE parameter to drop a schema containing one or more objects.

Syntax

DROP SCHEMA [ IF EXISTS ] [{namespace. | database. }]schema[,...] [ CASCADE | RESTRICT ]

Parameters

IF EXISTS: Specifies not to report an error if the schemas to drop do not exist. Use this clause in SQL scripts to avoid errors on dropping non-existent objects before attempting to create them.

{ database | namespace }

Name of the database or namespace that contains schema:

Database name: If specified, it must be the current database.
Namespace name (Eon Mode only): You must specify the namespace of objects in non-default namespaces. If no namespace is provided, it is assumed the object is in the default namespace.

schema

Name of the schema, by default public.

If you specify a schema name that contains a period, the part before the period cannot be the same as the name of an existing namespace.

CASCADE: Specifies to drop the schema and all objects in it, regardless of who owns those objects.

Caution
Objects in other schemas that depend on objects in the dropped schema—for example, user-defined functions—also are silently dropped.
RESTRICT: Drops the schema only if it is empty (default).

Privileges

Non-superuser: schema owner

Restrictions

You cannot drop the PUBLIC schema.
If a user is accessing an object within a schema that is in the process of being dropped, the schema is not deleted until the transaction completes.
Canceling a DROP SCHEMA statement can cause unpredictable results.

Examples

The following example drops schema S1 only if it doesn't contain any objects:

=> DROP SCHEMA S1;

The following example drops schema S1 whether or not it contains objects:

=> DROP SCHEMA S1 CASCADE;