Dropping Namespaces

You can drop a namespace only if you have been granted the DROP_ANY_NAMESPACE privilege, and the namespace has no associated tables. Also, you must have the appropriate privileges.

To drop a namespace:

DROP NAMESPACE [IF EXISTS] namespace_name [CASCADE]

Using the CASCADE option with DROP NAMESPACE lets you extend the activity to tables and other objects within the NAMESPACE.

Dropping a namespace is not an atomic operation, and completes the following steps:

• First check to make sure privileges to drop a namespace exist. Continue if privileges are in place.

• If no privileges exist, stop process with an error.

• If privileges are in place and CASCADE is not specified, check for tables, or other objects in the namespace. Drop the namespace if no objects exist.

• If tables or other objects exist in the namespace, stop process with an error.

• If privileges are in place to drop the namespace and CASCADE is specified, the statement drops the namespace, removing all tables, indexes related to the tables, and table privileges.

Note: You cannot drop the default namespace, sysdefault.