Table, Hash Index, and Join Index Attributes - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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 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 Teradata Vantage™ - Data Types and Literals, B035-1143 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 Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ - Temporal Table Support , B035-1182 .

Current ValidTime Unique? CHARACTER(2) Yes Reports whether a column is a UNIQUE Current VALIDTIME column.

For information about temporal columns, see Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ - Temporal Table Support , B035-1182 .

Sequenced ValidTime Unique? CHARACTER(2) Yes Reports whether a column is a UNIQUE Sequenced VALIDTIME column.

For information about temporal columns, see Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ - Temporal Table Support , B035-1182 .

NonSequenced ValidTime Unique? CHARACTER(2) Yes Reports whether a column is a UNIQUE Nonsequenced VALIDTIME column.

For information about temporal columns, see Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ - Temporal Table Support , B035-1182 .

Current TransactionTime Unique? CHARACTER(2) Yes Reports whether a column is a UNIQUE Current TRANSACTIONTIME column.

For information about temporal columns, see Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 and Teradata Vantage™ - Temporal Table Support , B035-1182 .

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 Object Name and Title Data in HELP Reports.

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