Adds or changes a comment about the following database objects:
o Catalog
o Collating sequence
o Column
o Constraint
o Domain
o Database
o Function
o Index
o Index partition
o Module
o Outline
o Procedure
o Profile
o Role
o Schema
o Sequence
o Storage map
o Storage map partition
o Synonym
o Table
o Trigger
o User
o View
SQL displays the comments on these objects when you issue a SHOW
statement.
1 – Environment
You can use the COMMENT ON statement:
o In interactive SQL
o Embedded in host language programs to be precompiled
o As part of a procedure in an SQL module
o In dynamic SQL as a statement to be dynamically executed
2 – Format
COMMENT ON --+--> objects-1 -+-> IS -+-> 'string' -+->
+--> objects-2 -+ +---- / <-----+
objects1 =
-+> <table-name> -> ( + <col-name> IS +-> 'string' -++> ) --+->
| | +---- / <-----+| |
| +----------- , <---------------+ |
+> CATALOG <catalog-name> ---------------------------------+
+> COLLATING SEQUENCE <col-sequence-name> -----------------+
+> COLUMN <table-name>.<col-name> -------------------------+
+> CONSTRAINT <constraint-name> ---------------------------+
+> DATABASE -+------------------------+--------------------+
| +-> ALIAS <alias-name> --+ |
+> DOMAIN <domain-name> -----------------------------------+
+> FUNCTION <function-name> -------------------------------+
+> INDEX <index-name> -+-------------------------------+---+
| +-> PARTITION <partition-name> -+ |
+> MODULE <module-name> -----------------------------------+
+> PROCEDURE <procedure-name> -----------------------------+
+> PROFILE <profile-name> ---------------------------------+
objects2 =
-+> ROLE <role-name> -----------------------------------------+-->
+> SCHEMA <schema-name> -------------------------------------+
+> SEQUENCE <sequence-name> ---------------------------------+
+> STORAGE MAP <map-name> +--------------------------------+-+
| +--> PARTITION <partition-name> -+ |
+> SYNONYM <synonym-name> -----------------------------------+
+> TABLE <table-name> ---------------------------------------+
+> TRIGGER <trigger-name> -----------------------------------+
+> USER <user-name> -----------------------------------------+
+> VIEW <view-name> -----------------------------------------+
3 – Arguments
3.1 – CATALOG
Names the catalog for which you want to create a comment. If
the catalog is not in the default schema, you must qualify the
catalog name in the COMMENT ON statement with an alias name.
3.2 – COLLATING_SEQUENCE
Names the collating sequence for which you want to create a
comment. If the collating sequence is not in the default schema,
you must qualify the collating sequence name in the COMMENT ON
statement with an alias name.
3.3 – COLUMN
Names the column for which you want to create a comment. You must
qualify the column name with a table name. If the column is not
in a table in the default schema, you must qualify the column
name in the COMMENT ON statement with both a table name and an
alias name.
3.4 – CONSTRAINT constraint-name
Names the constraint for which you want to create a comment. If
the constraint is not in the default schema, you must qualify the
constraint name in the COMMENT ON statement with an alias name.
3.5 – DATABASE
This statement writes the new comment to the database. If the
ALIAS clause is omitted, the default database is used. The alias-
name must be an alias specified by an ATTACH or CONNECT statement
during this session.
3.6 – DOMAIN
Names the domain for which you want to create a comment. If the
domain is not in the default schema, you must qualify the domain
name in the COMMENT ON statement with an alias name.
3.7 – FUNCTION
Names the function for which you want to create a comment. If
the function is not in the default schema, you must qualify the
function name in the COMMENT ON statement with an alias name.
3.8 – INDEX
Syntax options:
INDEX index-name
PARTITION partition-name
Names the index and, optionally, a partition in the named index,
for which you want to create a comment. If the index is not
in the default schema, you must qualify the index name in the
COMMENT ON statement with an alias name.
3.9 – IS string
Specifies the comment. SQL displays the text when it executes
a SHOW statement in interactive SQL. Enclose the comment within
single quotation marks (') and separate multiple lines in a
comment with a slash mark (/).
3.10 – MODULE
Names the module for which you want to create a comment. If the
module is not in the default schema, you must qualify the module
name in the COMMENT ON statement with an alias name.
3.11 – PROCEDURE
Names the procedure for which you want to create a comment. If
the procedure is not in the default schema, you must qualify the
procedure name in the COMMENT ON statement with an alias name.
3.12 – PROFILE
Names the profile for which you want to create a comment. If
the profile is not in the default schema, you must qualify the
profile name in the COMMENT ON statement with an alias name.
3.13 – ROLE
Names the role for which you want to create a comment. If the
role is not in the default schema, you must qualify the role name
in the COMMENT ON statement with an alias name.
3.14 – SCHEMA
Names the schema for which you want to create a comment. You must
create the schema first. If the schema is not in the default
schema, you must qualify the schema name in the COMMENT ON
statement with an alias name.
3.15 – SEQUENCE
Names the sequence for which you want to create a comment. If
the sequence is not in the default schema, you must qualify the
sequence name in the COMMENT ON statement with an alias name.
3.16 – STORAGE_MAP
Syntax options:
STORAGE MAP map-name
PARTITION partition-name
Names the storage map and, optionally, a vertical or horizontal
partition within that storage map, for which you want to create
a comment. If the storage map is not in the default schema, you
must qualify the storage map name in the COMMENT ON statement
with an alias name.
3.17 – SYNONYM
This performs the same function as the ALTER SYNONYM . . . COMMENT
IS syntax. The synonym-name must be the name of an existing
synonym. A database alias can be used to select a database other
than the default database alias.
3.18 – TABLE
Names the table for which you want to create a comment. If the
table is not in the default schema, you must qualify the table
name in the COMMENT ON statement with an alias name.
3.19 – table-name_col-name
Names the table and the column or columns in that table for which
you want to create a comment.
3.20 – TRIGGER
Names the trigger for which you want to create a comment. If
the trigger is not in the default schema, you must qualify the
trigger name in the COMMENT ON statement with an alias name.
3.21 – USER
Names the user (created with the CREATE USER statement) for
which you want to create a comment. If the user is not in the
default schema, you must qualify the user name in the COMMENT ON
statement with an alias name.
3.22 – VIEW
Names the view for which you want to create a comment. If the
view is not in the default schema, you must qualify the view name
in the COMMENT ON statement with an alias name.
4 – Examples
Example 1: Specifying a comment for columns and tables
SQL> -- Change the comment for the WORK_STATUS table:
SQL> COMMENT ON TABLE WORK_STATUS IS
cont> 'Links a status code with 1 of 3 statuses' ;
SQL> SHOW TABLE WORK_STATUS
Information for table WORK_STATUS
Comment on table WORK_STATUS: Links a status code with 1 of 3 statuses
.
.
.
SQL> -- Create a comment for the DEPARTMENT_CODE
SQL> -- column in the DEPARTMENTS table:
SQL> COMMENT ON COLUMN DEPARTMENTS.DEPARTMENT_CODE IS
cont> 'Also used in JOB_HISTORY table';
SQL> SHOW TABLE DEPARTMENTS
Information for table DEPARTMENTS
Comment on table DEPARTMENTS:
information about departments in corporation
Columns for table DEPARTMENTS:
Column Name Data Type Domain
----------- --------- ------
DEPARTMENT_CODE CHAR(4) DEPARTMENT_CODE_DOM
Comment: Also used in JOB_HISTORY table
.
.
.
Example 2: Specifying a comment containing more than one string
literal
SQL> COMMENT ON COLUMN EMPLOYEES.EMPLOYEE_ID IS
cont> '1: Used in SALARY_HISTORY table as Foreign Key constraint' /
cont> '2: Used in JOB_HISTORY table as Foreign Key constraint';
SQL> SHOW TABLE (COL) EMPLOYEES;
Information for table EMPLOYEES
Columns for table EMPLOYEES:
Column Name Data Type Domain
----------- --------- ------
EMPLOYEE_ID CHAR(5) ID_DOM
Comment: 1: Used in SALARY_HISTORY table as Foreign Key constraint
2: Used in JOB_HISTORY table as Foreign Key constraint
Primary Key constraint EMPLOYEES_PRIMARY_EMPLOYEE_ID
LAST_NAME CHAR(14) LAST_NAME_DOM
FIRST_NAME CHAR(10) FIRST_NAME_DOM
MIDDLE_INITIAL CHAR(1) MIDDLE_INITIAL_DOM
ADDRESS_DATA_1 CHAR(25) ADDRESS_DATA_1_DOM
ADDRESS_DATA_2 CHAR(20) ADDRESS_DATA_2_DOM
CITY CHAR(20) CITY_DOM
STATE CHAR(2) STATE_DOM
POSTAL_CODE CHAR(5) POSTAL_CODE_DOM
SEX CHAR(1) SEX_DOM
BIRTHDAY DATE VMS DATE_DOM
STATUS_CODE CHAR(1) STATUS_CODE_DOM
Example 3: Adding a Comment to a Trigger
SQL> COMMENT ON TRIGGER EMPLOYEE_ID_CASCADE_DELETE IS
cont> 'When an employee is deleted from EMPLOYEES, delete'/
cont> 'corresponding records from the other tables in the'/
cont> 'database.';
SQL> SHOW TRIGGER EMPLOYEE_ID_CASCADE_DELETE
EMPLOYEE_ID_CASCADE_DELETE
Source:
EMPLOYEE_ID_CASCADE_DELETE
BEFORE DELETE ON EMPLOYEES
(DELETE FROM DEGREES D WHERE D.EMPLOYEE_ID =
EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
(DELETE FROM JOB_HISTORY JH WHERE JH.EMPLOYEE_ID =
EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
(DELETE FROM SALARY_HISTORY SH WHERE SH.EMPLOYEE_ID =
EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW
-- Also, if an employee is terminated and that employee
-- is the manager of a department, set the manager_id
-- null for that department.
(UPDATE DEPARTMENTS D SET D.MANAGER_ID = NULL
WHERE D.MANAGER_ID = EMPLOYEES.EMPLOYEE_ID)
FOR EACH ROW.
.
.
.
Comment: When an employees is deleted from EMPLOYEES, delete
corresponding records from the other tables in the
database.
Example 4: Adding Comments to Multiple Columns in a Table
SQL> COMMENT ON JOBS (JOB_CODE is 'Required column',
cont> WAGE_CLASS is 'Valid values are: 1, 2, 3, or 4');
SQL> SHOW TABLE (COLUMNS) JOBS;
Information for table JOBS
Columns for table JOBS:
Column Name Data Type Domain
----------- --------- ------
JOB_CODE CHAR(4) JOB_CODE
Comment: Required column
Missing Value: None
WAGE_CLASS CHAR(1) WAGE_CLASS
Comment: Valid values are: 1, 2, 3, or 4
JOB_TITLE CHAR(20) JOB_TITLE
Missing Value: None
MINIMUM_SALARY INTEGER(2) SALARY
MAXIMUM_SALARY INTEGER(2) SALARY