ALTER SCHEMA (Databricks SQL)

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 } [, ...] ) |
     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.

  • OWNER TO principal

    Transfers ownership of the schema to principal.

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`