15.00 - HELP COLUMN Attributes - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

HELP COLUMN Attributes

The following table lists the attributes reported by a HELP COLUMN request.

 

Attribute

Data Type

Nullable?

Description

Column Name

CHARARACTER(30)

No

The name of a column for which HELP information is being returned.

Type

See “Data Type Codes” on page 901 for a list of the data type codes and their meanings.

CHARACTER(2)

Yes

The data type for data in the column.

With an explicit format, DATE is imported and exported according to that format, as indicated in the following bullets.

  • With an Implicit format created in INTEGERDATE mode, the export format is ‘YY/MM/DD’.
  • With an Explicit format created in ANSIDATE mode, the export format is ‘YYYY-MM-DD’.
  • The character data types Latin, Unicode, Graphic, KanjiSJIS, and Kanji1 are distinguished with respect to CF and CV by their respective value for the Character Type column.

    The type codes GF and GV are no longer returned for columns defined as CHARACTER(n) CHARACTER SET GRAPHIC.

    Nullable

    CHARACTER(1)

    Yes

    Indicates whether the column specified in Column Name is nullable.

  • N specifies that the column cannot be null.
  • Y specifies that the column can be null.
  • Format

    CHARACTER(30)

    Yes

    Display format for the data type.

    For a UDT, the displayed format is the format associated with the external type of the UDT.

    Data type display formats are documented in “Data Type Formats and Format Phrases” in SQL Data Types and Literals.

    Max Length

    INTEGER

    Yes

    Maximum amount of storage in bytes.

    MaxLength is the length of the external CharFix representation of the column, defined as CHARACTER(MaxLength) in USING clauses and any other place this needs to be specified.

    Note that while MaxLength is usually expressed as the internal size, presentation of the external size seems more appropriate for these data types.

    See SQL Data Types and Literals for each individual DateTime and Interval to determine the MaxLength values.

    For CHARACTER data, Max Length is the length of the column declaration in bytes.

    For column names, the maximum length is 256 bytes; however, the enforced maximum column name length is 60 bytes.

    For a UDT, Max Length is the maximum length of the external type associated with the UDT formatted as -(10)9.

    When DATEFORM=ANSIDATE, this column should be described externally as CHARACTER(10).

    Host data characteristics for these data types should be interpreted as the equivalent of CF, or CHARACTER(n) data type.

    You can use the StatementInfo parcel to return the description of items within the Data Field of the corresponding indicator mode Record parcels.

    For example, the FastLoad .TABLE command can be executed in Indicator Mode to get the additional information of data type and length of all table attributes. That information can then be used to fetch the data correctly in any release of Teradata Database.

    Decimal Total Digits

    SMALLINT

    Yes

    If type is DECIMAL.

    For INTERVAL types, returns a value from 1 - 4 representing the number of digits in the leading field of the interval (number of non‑fractional digits for INTERVAL SECOND).

    This column is null for TIME and TIMESTAMP data types.

    Decimal Fractional Digits

    SMALLINT

    Yes

    If type is DECIMAL.

    For TIME and TIMESTAMP types and all INTERVAL types with a SECOND field, this column returns a value of 0 - 6, indicating the fractional precision of seconds. For INTERVAL types without a SECOND field, the column returns null.

    Range Low

    FLOAT

    Yes

    Always null. BETWEEN clause now included in column constraint.

    Range High

    FLOAT

    Yes

    Always null. BETWEEN clause now included in column constraint.

    Uppercase

    CHARACTER(1)

    Yes

    Returned if type is CHARACTER or VARCHAR.

  • B specifies that column attributes for both UPPERCASE and CASESPECIFIC are defined.
  • C specifies that the column attributes for CASESPECIFIC is defined.
  • N specifies that column attributes for neither UPPERCASE nor CASESPECIFIC are defined.
  • U specifies that the column attributes for UPPERCASE is defined.
  • Table/View?

    CHARACTER(1)

    No

    Indicates whether the column specified by Column Name is from a table or a view.

  • T specifies that the column is from a table definition.
  • V specifies that the column is from a view definition.
  • Indexed?

    CHARACTER(1)

    Yes

    Identifies if the column is single‑column index.

  • N specifies that the column is not used as a single‑column index.
  • You can define multiple indexes on the same columns as long as they are different in some respect (value-ordered versus hash-ordered or non‑unique primary index on some set of columns and a USI on the same columns).

    In this case, the codes reported for Unique? and Primary? are for the index with the lowest index id. For a non‑unique partitioned primary index, a single column that also has a USI on that column displays N for Unique? and Y for Primary?

  • Y specifies that the column is used as a single‑column index.
  • A column can be a component of more than one index.

    Whether the value for this attribute is Y or N, the column might be a member of a column set that defines a composite index.

    Use HELP INDEX to see a list of all the primary and secondary indexes for a table.

    Unique?

    CHARACTER(1)

    Yes

    If the column defines a single‑column index, indicates whether the index is unique or non‑unique.

  • N specifies that the index is not unique.
  • Y specifies that the index is unique.
  • If INDEXED? attribute is N, then returns null.

    Primary?

    CHARACTER(1)

    Yes

    If the column defines a single‑column index, is that index a primary index or a secondary index?

  • P specifies that the column is a component of the primary index definition for the table.
  • S specifies that the column is a component of the secondary index definition for the table.
  • Multiple‑column indexes return a null result.

    If INDEXED? attribute is N, then returns null.

    Title

    VARCHAR (60)

    Yes

    The title (if a title exists) for the column about which HELP information is being returned.

    Column Constraint

    VARCHAR(255)

    Yes

    The text of the constraint clause, if a constraint is specified for the column.

    Character Type

    SMALLINT

    Yes

    Returns the character data type for a CHAR or VARCHAR column.

  • 1 specifies LATIN server character data type columns.
  • 2 specifies UNICODE server character data type columns.
  • 3 specifies KANJISJIS server character data type columns.
  • 4 specifies GRAPHIC server character data type columns.
  • 5 specifies KANJI1 server character data type columns.
  • If the data type of the column is not character, returns a null.

    IDCol Type

    CHARACTER(2)

    Yes

    Returns a code for the type of identity column.

  • GA specifies Generated Always.
  • GD specifies Generated by Default.
  • If the column is not an identity column, returns a null.

    UDT Name

    VARCHAR (61)

    Yes

    Returns the qualified type name for the UDT.

    Temporal Column

    This attribute only applies to temporal tables.

    CHARACTER(1)

    Yes

    Returns the temporal qualification, if any, for the column formatted as X(1) with the title Temporal Column.

  • Null specifies that the column is non‑temporal.
  • R specifies that the column is part of a temporal relationship constraint.
  • T specifies that the column has TRANSACTIONTIME column.
  • V specifies that the column has VALIDTIME column.
  • If the column is not temporal, returns null.

    For information about temporal columns, see ANSI Temporal Table Support and Temporal Table Support.

    Current ValidTime Unique

    This attribute only applies to temporal tables.

    CHARACTER(1)

    Yes

    Reports whether a VALIDTIME column is Current and a UNIQUE constraint or not.

    If the value is:

  • Null, the column is non‑temporal.
  • N, the column is not VALIDTIME and UNIQUE.
  • Y, the column is VALIDTIME and UNIQUE.
  • For information about temporal columns, see ANSI Temporal Table Support and Temporal Table Support.

    Sequenced ValidTime Unique

    This attribute only applies to temporal tables.

    CHARACTER(1)

    Yes

    Reports whether a VALIDTIME column is Sequenced and a UNIQUE constraint or not.

    If the value is:

  • Null, the column is non‑temporal.
  • N, the column is not Sequenced VALIDTIME UNIQUE.
  • Y, the column is Sequenced VALIDTIME UNIQUE.
  • For information about temporal columns, see ANSI Temporal Table Support and Temporal Table Support.

    Nonsequenced ValidTime Unique

    This attribute only applies to temporal tables.

    CHARACTER(1)

    Yes

    Reports whether a VALIDTIME column is Nonsequenced and a UNIQUE constraint or not. If the value is:

  • Null, the column is non‑temporal.
  • N, the column is not Nonsequenced VALIDTIME UNIQUE.
  • Y, the column is Nonsequenced VALIDTIME UNIQUE.
  • For information about temporal columns, see ANSI Temporal Table Support and Temporal Table Support.

    Current TransactionTime Unique

    This attribute only applies to temporal tables.

    CHARACTER(1)

    Yes

    Reports whether a TRANSACTIONTIME column is Current and a UNIQUE constraint or not. If the value is:

  • Null, the column is non‑temporal.
  • N, the column is not Current TRANSACTIONTIME UNIQUE.
  • Y, the column is Current TRANSACTIONTIME UNIQUE.
  • For information about temporal columns, see ANSI Temporal Table Support and Temporal Table Support.

    Partitioning Column

    This attribute only applies to column‑partitioned tables and join indexes.

    CHARACTER(1) LATIN UPPERCASE NOT CASESPECIFIC NOT NULL

    No

    Identifies whether the column is the partitioning column.

  • N means the column is not a partitioning column.
  • Y means the column is a partitioning column in a partitioning expression.
  • Column Partition Number

    This attribute applies to column‑partitioned tables and join indexes.

    BIGINT NOT NULL

    No

    When a column or column expression is column-partitioned, identifies the number of the column partition to which the column belongs.

  • 0 means the column or column expression is not column‑partitioned.
  • Anything other than 0 means the value describes the number of the column partition to which the column belongs.
  • The format for Column Partition Number is ‘ZZZZZZZZZZZZZZZZZZ9’.

    Note: Columns of a column‑partitioned table or join index that have the same column partition number belong to the same column partition.

    Column Partition Format

    This attribute applies to column‑partitioned tables and join indexes.

    CHARACTER(2) LATIN UPPERCASE NOT CASESPECIFIC NOT NULL

    No

    The format for a column partition.

  • CS means system‑determined COLUMN format.
  • CU means user‑specified COLUMN format.
  • NA means not applicable.
  • The attribute describes a column expression or the column is not a member of a column partition.

  • RS means system‑determined ROW format.
  • RU means user‑specified ROW format.
  • Column Partition AC

    This attribute applies to column‑partitioned tables and join indexes.

    CHARACTER(2) LATIN UPPERCASE NOT CASESPECIFIC NOT NULL

    No

    Indicates whether a column that is part of a column partition is auto-compressed.

  • AC means auto-compressed.
  • NA means not applicable.
  • The attribute describes a column expression or the column is not a member of a column partition.

  • NC means not auto-compressed.
  • Derived_UDT

    This attribute applies to derived Period columns.

    CHARACTER(2) LATIN UPPERCASE NOT CASESPECIFIC NOT NULL

    No

    This attribute applies to derived Period columns.

  • Null means one of two possible things.
  • The column is not a derived Period column.
  • The column is not a component of a derived Period column.
  • PB means derived Period column BEGIN.
  • PE means derived Period column END.
  • PP means derived Period column.
  • Derived_UDTFieldID

    SMALLINT

    Yes

    This attribute applies to derived Period columns and either reports the fieldid of the derived UDT column or null.

    Null means one of two possible things.

  • The column is a derived Period column.
  • The column is not a component of a derived Period column.
  • Column Dictionary Name

    VARCHAR(128)

    No

    The attributes shown in this section are based upon and supersede the corresponding older attributes, Column Name, Title, UDT Database and UDT Name, while providing additional functionality.

    The older attributes are retained for compatibility with existing applications.

    For details, see the topics beginning with “Working with Object Name and Title Data in HELP Reports” on page 896.

    Column SQL Name

    VARCHAR(644)

    Column Name UEscape

    VARCHAR(1)

    Yes

    Dictionary Title

    VARCHAR(256)

    Yes

    SQL Title

    VARCHAR(1256)

    ?

    Title UEscape

    VARCHAR(1)

    Yes

    UDT Database Dictionary Name

    VARCHAR(128)

    ?

    UDT Database SQL Name

    VARCHAR(644)

    ?

    UDT Database Name UEscape

    VARCHAR(1)

    Yes

    UDT Dictionary Name

    VARCHAR(128)

    No

    (continued)

    UDT SQL Name

    VARCHAR(644)

    UDT Name UEscape

    VARCHAR(1)

    Yes