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.

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, will restrict dropping a non-empty schema and is enabled by default.

  • CASCADE

    If specified, will drop all the associated tables and functions.

Warning

To avoid accidental data loss, do not register a schema (database) to a location with existing data or create new external tables in a location managed by a schema.

Dropping a schema using the CASCADE option recursively deletes all data 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;