15.00 - Standard Form of Display for SHOW TABLE - 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)

Standard Form of Display for SHOW TABLE

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, Teradata Database 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 Database Design for the rules Teradata Database 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.