DROP SCHEMA

Applies to: Databricks SQL 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.

  Note

  In Unity Catalog, dropping a schema using CASCADE soft-deletes the schema and its child objects. Data files for managed tables and volumes are retained to allow for a 7-day recovery window. After the recovery window ends, the data files are permanently deleted (purged) within 48 hours. Files for external tables and external volumes are not deleted. For details, see Object storage lifecycle in Unity Catalog.

  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;

Related articles

• CREATE SCHEMA
• DESCRIBE SCHEMA
• SHOW SCHEMAS
• Object storage lifecycle in Unity Catalog