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. In Unity Catalog, dropping a schema using CASCADE soft-deletes tables: managed table files will be cleaned up within 7 to 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;

Related articles

• CREATE SCHEMA
• DESCRIBE SCHEMA
• SHOW SCHEMAS