15.00 - Table, Hash Index, and Join Index 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)

Table, Hash Index, and Join Index Attributes

A HELP TABLE request returns a set of attributes for each column in the specified table, as shown in the following table of attributes.

The columns or parameters are listed in the order in which they were defined.

 

Attribute

Data Type

Nullable?

Comments

Column Name

VARCHAR(30)

No

The name of a column in the specified table.

Interpret as the equivalent of CF, or CHARACTER(n) data type.

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

Type

CHARACTER(2)

No

The data type for Column Name.

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.

Default output format for the code is X(2).

See “Data Type Codes” on page 901 for a complete list of data types and codes.

Comment

VARCHAR (255)

Yes

The contents of the optional comment field for the table, if a comment was created

Nullable

CHARACTER

Yes

Whether the column accepts nulls.

  • N specifies that the column does not accept nulls.
  • Y specifies that the column accepts nulls.
  • Format

    VARCHAR(30)

    Yes

    The format for Column Name.

    The CHARACTER SET attribute for FORMAT is always Unicode regardless of the system language support mode and session character set.

    With an explicit format, DATE.

    The general rule for reporting column FORMAT attributes by HELP TABLE is that a format string is only reported when it does not match the default format for the column.

    The exception to this rule is columns that have the DATE data type. In this case, the system always reports the FORMAT string.

    With an implicit format created in INTEGERDATE mode, ‘YY/MM/DD’.

    With an implicit format created in ANSIDATE mode, ‘YYYY/MM/DD’.

    Title

    VARCHAR(60)

    Yes

    The title for the column, if a title exists.

    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.

    MaxLength for the DATE type is 4, representing the internal storage size.

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

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

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

    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

    Formatted as -(5)9

    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

    Formatted as -(5)9

    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 is included in column constraint.

    Range High

    FLOAT

    Yes

    Always null. BETWEEN clause is included in column constraint.

    Uppercase

    CHARACTER

    Yes

    Reported if the column data 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.
  • Otherwise null.

    Table/View?

    CHARACTER

    No

    Object type.

    The value reported depends on whether the request is HELP HASH INDEX, HELP JOIN INDEX, or HELP TABLE.

  • I specifies join index.
  • N specifies hash index.
  • O specifies NoPI table.
  • Q specifies queue table.
  • T specifies base table.
  • Default Value

    VARCHAR (255)

    Yes

    Returns the default value (as text) for the parameter.

    The value is null if there is no default value.

    Character Type

    SMALLINT

    Yes

    Returns a code for the server character set for the 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 null.

    IDCol Type

    CHARACTER(2)

    Yes

    Returns a code for the type of identity column.

  • GA specifies Generated Always.
  • GD specifies Generated by Default.
  • UDT Name

    VARCHAR(61)

    Yes

    Returns the unqualified name of the UDT.

    Temporal Column

    CHARACTER(2)

    Yes

    Reports whether a column is temporal and if it is part of a Temporal Relationship constraint.

  • N specifies a non‑temporal column.
  • R specifies that the column is part of a temporal relationship constraint.
  • T specifies a TRANSACTIONTIME column.
  • V specifies a VALIDTIME column.
  • For information about temporal columns, see ANSI Temporal Table Support and Temporal Table Support.

    Current ValidTime Unique?

    CHARACTER(2)

    Yes

    Reports whether a column is a UNIQUE Current VALIDTIME column.

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

    Sequenced ValidTime Unique?

    CHARACTER(2)

    Yes

    Reports whether a column is a UNIQUE Sequenced VALIDTIME column.

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

    NonSequenced ValidTime Unique?

    CHARACTER(2)

    Yes

    Reports whether a column is a UNIQUE Nonsequenced VALIDTIME column.

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

    Current TransactionTime Unique?

    CHARACTER(2)

    Yes

    Reports whether a column is a UNIQUE Current TRANSACTIONTIME column.

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

    Column Dictionary Name

    VARCHAR(128)

    No

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

    The older attributes are retained for compatibility with existing applications.

    The system returns this set of attributes for each column in the HELP TABLE.

    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)

    No

    Column Name UEscape

    VARCHAR(1)

    Yes

    Dictionary Title

    VARCHAR(256)

    No

    SQL Title

    VARCHAR(1284)

    No

    Title UEscape

    VARCHAR(1)

    Yes

    UDT Dictionary Name

    VARCHAR(128)

    Yes

    UDT SQL Name

    VARCHAR(644)

    Yes

    UDT Name UEscape

    VARCHAR(1)

    Yes