Categories:

DDL Commands

COMMENT

Adds a comment or overwrites an existing comment for an existing object.

Comments can be added to all objects (users, roles, warehouses, databases, tables, etc.). Comments can be added to individual table columns as well.

Syntax

COMMENT [IF EXISTS] ON <object_type> <object_name> IS '<string_literal>';

COMMENT [IF EXISTS] ON COLUMN <table_name>.<column_name> IS '<string_literal>';

Usage Notes

  • In addition to this command, a comment can be added when creating or altering an object:

  • A slightly different syntax is used for adding comments to table columns:

    • A comment can be added at creation by following the column declaration with the COMMENT keyword (not property).

    • The comment can then be changed using this command.

Examples

Initially create a schema with a comment, then overwrite the comment:

CREATE SCHEMA my_schema COMMENT='this is comment1';

SHOW SCHEMAS LIKE '%schema%';

+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------+
| created_on                    | name               | is_default | is_current | database_name | owner        | comment                                                   | options | retention_time |
|-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------|
| 2018-06-26 11:06:55.994 -0700 | INFORMATION_SCHEMA | N          | N          | YDB           |              | Views describing the contents of schemas in this database |         | 1              |
| 2018-06-26 11:06:30.532 -0700 | MY_SCHEMA          | N          | Y          | YDB           | SYSADMIN     | this is comment1                                          |         | 1              |
+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------+

COMMENT ON SCHEMA my_schema IS 'now comment2';

SHOW SCHEMAS LIKE '%schema%';

+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------+
| created_on                    | name               | is_default | is_current | database_name | owner        | comment                                                   | options | retention_time |
|-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------|
| 2018-06-26 11:06:55.994 -0700 | INFORMATION_SCHEMA | N          | N          | YDB           |              | Views describing the contents of schemas in this database |         | 1              |
| 2018-06-26 11:06:30.532 -0700 | MY_SCHEMA          | N          | Y          | YDB           | SYSADMIN     | now comment2                                              |         | 1              |
+-------------------------------+--------------------+------------+------------+---------------+--------------+-----------------------------------------------------------+---------+----------------+

Create a table with a comment on a table column, then overwrite the comment:

CREATE TABLE my_table(my_column string COMMENT 'this is comment3');

DESC TABLE my_table;

+-----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+------------------+
| name      | type              | kind   | null? | default | primary key | unique key | check | expression | comment          |
|-----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+------------------|
| MY_COLUMN | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | this is comment3 |
+-----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+------------------+

COMMENT ON COLUMN my_table.my_column IS 'now comment4';

DESC TABLE my_table;

+-----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+--------------+
| name      | type              | kind   | null? | default | primary key | unique key | check | expression | comment      |
|-----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+--------------|
| MY_COLUMN | VARCHAR(16777216) | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | now comment4 |
+-----------+-------------------+--------+-------+---------+-------------+------------+-------+------------+--------------+

Create a view with a comment, then overwrite the comment:

CREATE VIEW my_view comment='this is comment5' AS (SELECT * FROM my_table);

SHOW VIEWS LIKE 'my_view';

+-------------------------------+---------+----------+---------------+-------------+----------+------------------+-----------------------------------------------------------------------------+-----------+
| created_on                    | name    | reserved | database_name | schema_name | owner    | comment          | text                                                                        | is_secure |
|-------------------------------+---------+----------+---------------+-------------+----------+------------------+-----------------------------------------------------------------------------+-----------|
| 2018-06-26 11:11:01.048 -0700 | MY_VIEW |          | YDB           | MY_SCHEMA   | SYSADMIN | this is comment5 | CREATE VIEW my_view comment='this is comment5' AS (SELECT * FROM my_table); | false     |
+-------------------------------+---------+----------+---------------+-------------+----------+------------------+-----------------------------------------------------------------------------+-----------+

COMMENT ON VIEW my_view IS 'now comment6';

SHOW VIEWS LIKE 'my_view';

+-------------------------------+---------+----------+---------------+-------------+----------+--------------+-----------------------------------------------------------------------------+-----------+
| created_on                    | name    | reserved | database_name | schema_name | owner    | comment      | text                                                                        | is_secure |
|-------------------------------+---------+----------+---------------+-------------+----------+--------------+-----------------------------------------------------------------------------+-----------|
| 2018-06-26 11:11:01.048 -0700 | MY_VIEW |          | YDB           | MY_SCHEMA   | SYSADMIN | now comment6 | CREATE VIEW my_view comment='this is comment5' AS (SELECT * FROM my_table); | false     |
+-------------------------------+---------+----------+---------------+-------------+----------+--------------+-----------------------------------------------------------------------------+-----------+