ALTER SCHEMA

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

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 | DATABASE schema_name
   { SET DBPROPERTIES ( { key = val } [, ...] ) |
     [ SET ] OWNER TO principal }

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.

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`