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 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.
-
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 usingCASCADE
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;