COMMENT (Comment-Retrieving Form)

Purpose

Retrieves a previously placed comment on a database object or definition.

Syntax

where:

Syntax Element …	Specifies …
object_kind_1	a mandatory database object kind specification. The valid specifications for object_kind_1 are not all database objects. Triggers and views, for example, are definitions of actions or conceptual groups rather than database objects. You must specify the following database object kinds to retrieve a comment for the kind of object they represent. COLUMN FUNCTION GLOP SET GROUP MACRO METHOD PROCEDURE PROFILE ROLE TRIGGER TYPE VIEW
object_kind_2	an optional database object kind specification. You can specify the following database object kinds to retrieve a comment for the kind of object they represent, but they are optional. DATABASE TABLE USER FILE The semantics of the COMMENT statement require that if you specify an object kind, you must specify the keyword TABLE for any hash or join index whose comment you want to retrieve; however, because TABLE is not a mandatory object kind specification, you are not required to specify it. If you omit the object_kind when you retrieve a comment for a file, the object_name must contain the whole path to the file, for example, dbname.uifname.
database_name \| user_name	the containing database or user for object_name if it is not contained by the current database or user. You cannot specify a database name or user name if object_kind is GROUP. For the rules to use in naming database objects, see SQL Fundamentals.
object_name	the name of the object for which a comment is to be retrieved. For the rules to use in naming database objects, see SQL Fundamentals. The specified object can be any of the following: A parameter in a macro, stored procedure, or user‑defined function. A column in a user base table, error table, hash index, join index, or view. A specific function, macro, profile, role, stored procedure, base table, error table, hash index, join index, trigger, or view name contained by a database or user. A database or user. A UDT. You can retrieve a comment on a particular attribute of a structured UDT by specifying database_name.udt_name.attribute_name. A method. If you specify a method, you must use its specific method name. A GLOP set. If no object kind keyword precedes the object name, then Teradata Database attempts to deduce the object from the level of qualification in the name. Use the fully qualified name if there is any ambiguity. Let x.y.z indicate the qualification hierarchy, with x being the highest, or coarsest grained, level and z the lowest, or most finely grained.
object_name (continued)	The following bullets describe the objects implied by the various qualification levels: If you specify a hierarchy level of x, then the specified object is implied to be one of the following. Database User If you specify a hierarchy level of x.y, then the specified object is implied to be one of the following. Base table Error table GLOP set Hash index Join index Macro Profile Role Stored procedure Trigger User‑defined function View within database or user x. If you specify a hierarchy level of x.y.z, then the specified object is implied to be one of the following. Macro parameter Stored procedure parameter Structured UDT attribute Table column UDF parameter View column within GLOP set, UDF, UDT, macro, stored procedure, profile, role, base table, trigger, or view y which, in turn, is within database or user x.

ANSI Compliance

COMMENT is a Teradata extension to the ANSI SQL:2011 standard.

Required Privileges

None.

Privileges Granted Automatically

None.

Rules for Using COMMENT (Comment-Retrieving Form)

These rules apply to using the comment‑retrieving form of the COMMENT statement:

COMMENT (Comment‑Retrieving Form) is treated as a DML statement for transaction processing, so it can be used in 2PC session mode. For a brief description of the two‑phase commit protocol, see Introduction to Teradata.

You can specify a COMMENT (Comment‑Retrieving Form) request at any point within the boundaries of an explicit transaction in Teradata session mode.

To retrieve a comment, do not specify a comment string following the specification of the object name.

IF you …	THEN the system …
specify a comment string	does one of the following things: If the specified object has no existing comment, then Teradata Database places the specified comment string for the object in the data dictionary. If the specified object has an existing comment, then Teradata Database replaces the existing comment in the data dictionary with the newly specified string.
do not specify a comment string	does one of the following things: If the specified object has no existing comment, then Teradata Database returns a null. If the specified object has an existing comment, then Teradata Database returns the text of that comment as it is stored in the data dictionary.

You cannot specify a containing database or user name if the object kind you specify is GROUP.

Example : Retrieving a Comment

Suppose you have placed the following comment describing the name column in the employee table:

     COMMENT ON COLUMN employee.name

       IS 'Employee name, last name followed by first initial';

The following request retrieves this comment:

     COMMENT ON COLUMN employee.name;

The request returns the comment string as created by the previous request:

     Employee name, last name followed by first initial

Note that because the object kind for this request is COLUMN, you must specify the keyword COLUMN when you code it.

Example

Suppose you have defined a hash index named ord_hidx defined on the orders table, which is contained in the accounting database.

Because the object kind for this request is TABLE, representing a hash index, you are not required to specify TABLE in the request.

You could type the following comment‑placing COMMENT request to define a comment on the hash index ord_hidx:

     COMMENT accounting.ord_hidx AS 'hash index on Orders';

Then if you were to perform the following comment‑retrieving COMMENT request, the text that follows would be returned to you:

     COMMENT accounting.ord_hidx;

     hash index on orders

COMMENT (Comment-Retrieving Form) - Teradata Database

SQL Data Manipulation Language