ALTER SCHEMA
Applies to: Databricks SQL
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
-
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:
Databricks SQL SQL warehouse version 2022.35 or higher
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:
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 Runtime 13.3 LTS and above
tag_name
A literal
STRING
. Thetag_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');