ALTER SCHEMA

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Changes the owner of a schema or alters metadata associated with a schema by setting DBPROPERTIES. The specified property values override any existing value with the same property name. An error message is issued if the schema is not found in the system. This command is mostly used to record the metadata for a schema and may be used for auditing purposes.

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

Syntax 

ALTER SCHEMA schema_name
   { SET DBPROPERTIES ( { key = val } [, ...] ) |
    [ SET ] OWNER TO principal
    SET TAGS ( { tag_name = tag_value } [, ...] ) |
    UNSET TAGS ( tag_name [, ...] ) } }

Parameters

  • schema_name

    The name of the schema to be altered.

  • DBPROPERTIES ( key = val [, …] )

    The schema properties to be set or unset.

  • [ SET ] OWNER TO principal

    Transfers ownership of the schema to principal.

    Applies to: check marked yes Databricks SQL SQL warehouse version 2022.35 or higher check marked yes Databricks Runtime 11.2 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: check marked yes 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: check marked yes 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.

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');