SHOW TABLE Usage | Teradata Vantage - 17.10 - Standard Form of Display for SHOW TABLE - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)
The following items describe the standard display form for SHOW TABLE results.
  • SHOW TABLE always indicates whether the table was created as SET (no duplicate rows) or MULTISET (duplicate rows if no uniqueness defined).
  • SHOW TABLE displays fallback and journal status of the table.
  • SHOW TABLE does not return the default FORMAT specifications for the columns in a table; it reports only those FORMAT specifications that differ from the defaults.

    This means that SHOW TABLE returns an equivalent, but not necessarily identical, create text to that specified for the defining CREATE TABLE request.

  • Named constraints become either table-level constraints or are mapped to unique indexes. The display reflects the implementation.

    This means that regardless of the setting for DisableNoPI, when you declare an explicit PRIMARY KEY for a table, but do not declare either an explicit PRIMARY INDEX or NO PRIMARY INDEX, the SHOW TABLE listing for that table does not report the specified primary key as a PRIMARY KEY constraint. Instead, Vantage reports what you had specified as the PRIMARY KEY as a UPI. Because field_3 is neither declared as a PRIMARY KEY constraint nor as a UNIQUE constraint, the system does not change its definition in the SQL create text. See Teradata Vantage™ - Database Design, B035-1094 for the rules Vantage follows when making these conversions.

  • If you do not define a MERGEBLOCKRATIO value for a table, a SHOW TABLE request returns the CREATE TABLE SQL text as if you had specified DEFAULT MERGEBLOCKRATIO, which is the default value for the option.
  • SHOW TABLE displays character constant strings, including TITLE, COMPRESS, and DEFAULT values, as follows:
    FOR this type of constant... The value is displayed in this format...
    printable single byte character Teradata Latin.
    anything else internal Teradata hexadecimal.
  • The general rule for reporting column FORMAT attributes by SHOW 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.

  • If the table is a temporal table, the temporal columns are returned in the form AS VALIDTIME and AS TRANSACTIONTIME in the report.