HELP COLUMN 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™

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

Attribute Data Type Nullable? Description
Column Name CHARACTER(30) No The name of a column for which HELP information is being returned.
Type

See Data Type Codes for a list of the data type codes and descriptions.

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 Teradata Vantage™ - Data Types and Literals, B035-1143.

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

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

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

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

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, column is non-temporal.
  • N, column is not Current TRANSACTIONTIME UNIQUE.
  • Y, column is Current TRANSACTIONTIME UNIQUE.

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

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, column is not a partitioning column.
  • Y, 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’.

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 that the column is not a derived Period column or 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 Object Name and Title Data in HELP Reports.

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  
UDT SQL Name VARCHAR(644)
UDT Name UEscape VARCHAR(1) Yes