DROP SCHEMA

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Drops a schema and deletes the directory associated with the schema from the file system. An exception is thrown if the schema does not exist in the system. To drop a schema you must have the MANAGE privilege on the schema or be its owner.

While usage of SCHEMA and DATABASE is interchangeable, SCHEMA is preferred.

Syntax 

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

Parameters

  • IF EXISTS

    If specified, no exception is thrown when the schema does not exist.

  • schema_name

    The name of an existing schemas in the system. If the name does not exist, an exception is thrown.

  • RESTRICT

    If specified, restricts dropping a non-empty schema and is enabled by default.

  • CASCADE

    If specified, drops all the associated tables and functions recursively. In Unity Catalog, dropping a schema using CASCADE soft-deletes tables: managed table files will be cleaned up after 30 days, but external files are not deleted. Warning! If the schema is managed by the workspace-level Hive metastore, dropping a schema using CASCADE recursively deletes all files in the specified location, regardless of the table type (managed or external).

Examples 

-- Create `inventory_schema` Database
> CREATE SCHEMA inventory_schema COMMENT 'This schema is used to maintain Inventory';

-- Drop the schema and its tables
> DROP SCHEMA inventory_schema CASCADE;

-- Drop the schema using IF EXISTS
> DROP SCHEMA IF EXISTS inventory_schema CASCADE;