ALTER SCHEMA

Applies to: check marked yes Databricks SQL Databricks Runtime

Changes the owner of a schema, changes the managed storage location of a schema, sets predictive optimization behavior, alters metadata associated with a schema by setting DBPROPERTIES, or sets the recovery period for dropped managed tables. The specified property values override any existing value with the same property name.

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

Syntax

ALTER SCHEMA schema_name
    { DEFAULT COLLATION default_collation_name |
      SET DBPROPERTIES ( { key = val } [, ...] ) |
      [ SET ] OWNER TO principal
      SET TAGS ( { tag_name = tag_value } [, ...] ) |
      UNSET TAGS ( tag_name [, ...] ) |
      { ENABLE | DISABLE | INHERIT } PREDICTIVE OPTIMIZATION |
      SET MANAGED LOCATION location |
      [ SET ] RETAIN DROPPED TO number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS } }

Parameters

schema_name

The name of the schema to be altered. If the schema cannot be found, Azure Databricks raises a SCHEMA_NOT_FOUND error.
DEFAULT COLLATION default_collation_name

Applies to: Databricks SQL Databricks Runtime 17.1 and above

Changes the default collation for new objects defined within the schema. The default collation of existing objects within the schema is not changed.
DBPROPERTIES ( key = val [, …] )

The schema properties to be set or unset.
[ SET ] OWNER TO principal

Transfers ownership of the schema to principal.

Applies to: Databricks SQL Databricks Runtime 11.3 LTS and above

SET is allowed as an optional keyword.
SET TAGS ( { tag_name = tag_value } [, …] )

Apply tags to the schema. You need to have USE SCHEMA to apply a tag to the schema.

Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above
UNSET TAGS ( tag_name [, …] )

Remove tags from the schema. You need to have USE SCHEMA to remove a tag from the schema.

Applies to: Databricks SQL Databricks Runtime 13.3 LTS and above
tag_name

A literal STRING. The tag_name must be unique within the schema.
tag_value

A literal STRING.
{ ENABLE | DISABLE | INHERIT } PREDICTIVE OPTIMIZATION

Applies to: Databricks SQL Databricks Runtime 12.2 LTS and above Unity Catalog only

Alters the schema to the desired predictive optimization setting. By default, when schemas are created, the behavior is to INHERIT from the catalog. By default, new objects created in the schema will inherit the setting from the schema.

If the schema is altered, the behavior will cascade to all objects that inherit predictive optimization. Objects which explicitly ENABLE or DISABLE predictive optimization are not affected by the schema setting.

To set predictive optimization for a schema, the user must have CREATE permission on the schema.

Only schemas in non-OpenSharing, managed catalogs in Unity Catalog are eligible for predictive optimization.
SET MANAGED LOCATION location

Applies to: Databricks SQL Databricks Runtime 18.1 and above Unity Catalog only

Changes the managed storage location for a standard schema. New managed tables and managed volumes created in the schema use the updated location. Databricks does not move existing objects.
- location
  
  The cloud storage path for the new managed storage location. It must be contained within an external location.
[ SET ] RETAIN DROPPED TO number { HOUR | HOURS | DAY | DAYS | WEEK | WEEKS }

Applies to: Databricks Runtime 17.3 and above Unity Catalog only

Important

This feature is in Public Preview.

Sets the recovery period for dropped managed tables in the schema — the period during which dropped tables can be recovered using the UNDROP TABLE command. The default inherits from the parent catalog (7 days if not set). The value must be 0 hours (to disable recovery) or between 7–30 days, inclusive.

Setting the recovery period to 0 means dropped tables are not recoverable using UNDROP and data files are deleted from cloud storage within 48 hours.

The schema-level recovery period takes precedence over any catalog-level setting for tables in the schema. This setting only applies to tables dropped after the recovery period is configured. After the recovery period ends, data files for managed tables are deleted from your cloud storage within 48 hours.

SET is allowed as an optional keyword. Requires MANAGE privilege or ownership on the schema. See also Drop a managed table.

Examples

-- Creates a schema named `inventory`.
> CREATE SCHEMA inventory;

-- Alters the schema to set properties `Edited-by` and `Edit-date`.
> ALTER SCHEMA inventory SET DBPROPERTIES ('Edited-by' = 'John', 'Edit-date' = '01/01/2001');

-- Verify that properties are set.
> DESCRIBE SCHEMA EXTENDED inventory;
 database_description_item                 database_description_value
 ------------------------- ------------------------------------------
             Database Name                                  inventory
               Description
                  Location    file:/temp/spark-warehouse/inventory.db
                Properties ((Edit-date,01/01/2001), (Edited-by,John))

-- Transfer ownership of the schema to another user
> ALTER SCHEMA inventory OWNER TO `alf@melmak.et`

-- Applies three tags to the schema named `test`.
> ALTER SCHEMA test SET TAGS ('tag1' = 'val1', 'tag2' = 'val2', 'tag3' = 'val3');

-- Removes three tags from the schema named `test`.
> ALTER SCHEMA test UNSET TAGS ('tag1', 'tag2', 'tag3');

-- Enables predictive optimization for objects in schema my_schema
> ALTER SCHEMA my_schema ENABLE PREDICTIVE OPTIMIZATION;

-- Alter the default collation of a schema
> ALTER SCHEMA my_schema DEFAULT COLLATION UNICODE_CI_AI;

-- Change the managed storage location of a schema
> ALTER SCHEMA my_catalog.my_schema SET MANAGED LOCATION 's3://my-bucket/schemas/my_schema/';

-- Set a 14-day recovery period for dropped managed tables in a schema
> ALTER SCHEMA my_catalog.my_schema RETAIN DROPPED TO 14 DAYS;

-- Set a 7-day recovery period (overrides any catalog-level setting)
> ALTER SCHEMA my_catalog.my_schema SET RETAIN DROPPED TO 7 DAYS;

Feedback

Was this page helpful?

Last updated on 2026-06-16