17.05 - Exceptions to the SHOW TABLE Table Definition - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)

SHOW TABLE displays a standardized CREATE TABLE statement that would create the named table with the following exceptions.

Because a standard display form is used, the result might not be identical to the text used to create the table. The SHOW TABLE display is such that if the table were dropped and created using the SHOW TABLE output, the result would be a table with structure identical to the one shown.

Table Modifications, Indexes, and SHOW TABLE

If the table has been modified using the ALTER TABLE or CREATE INDEX statements, modifications are reflected in the CREATE TABLE request displayed.

SHOW TABLE displays all index and constraint information for the table. There is an upper limit of 31,744 characters that SHOW TABLE can display.

Column Grouping, Partitioning, and SHOW TABLE

If a column grouping is output as part of the COLUMN clause for any of the following cases, columns and multicolumn groups are separated by commas, with a space following a comma, and ordered by the field ID of the first column of each column partition and, within a column partition, by the field IDs of the columns in the column partition. An AUTO COMPRESS or NO AUTO COMPRESS option is preceded by a space. A COLUMN or ROW format keyword is not followed by a space for a column group. ALL BUT is followed by a space.

For a column-partitioned table, the SQL text returned by SHOW TABLE includes a PARTITION BY clause with a COLUMN clause. Grouping, if any, is included in the COLUMN clause, not in the column list.

Column Autocompression and SHOW TABLE

If all of the column partitions are single-column partitions with system-determined format and NO AUTO COMPRESS, Vantage reports COLUMN NO AUTO COMPRESS for the COLUMN clause.

If all of the column partitions are single-column partitions except for one that is multicolumn, and all column partitions have system-determined format and AUTO COMPRESS, Vantage reports the shorter in length between COLUMN AUTO COMPRESS ALL BUT ((list_of_columns_in_the_multicolumn_partition )) or COLUMN AUTO COMPRESS (list_of_the_columns_in_the_single-column_partitions ) for the COLUMN clause.

If all the column partitions are single-column partitions except for one that is multicolumn and all column partitions have system-determined format and NO AUTO COMPRESS, Vantage reports the shorter in length of COLUMN NO AUTO COMPRESS ALL BUT ((list_of_columns_in_the_multicolumn_partition)) or COLUMN NO AUTO COMPRESS (list_of_the_columns_in_the_single-column_partitions) for the COLUMN clause.

If none of the preceding cases apply and at least one, but not all, of the column partitions are single-column partitions with system-determined format and AUTO COMPRESS, Vantage reports COLUMN ALL BUT ((list_of_single_columns ) or (list_of_multiple_columns ) or both with any applicable options) that specifies all the column partitions except for the single-column partitions with system-determined format and AUTO COMPRESS for the COLUMN clause.

If none of the preceding cases apply and at least one, but not all, of the column partitions are single-column partitions with system-determined format, Vantage reports the shorter in length of the following for the COLUMN clause:
  • COLUMN AUTO COMPRESS ALL BUT ((list_of_single_columns) or (list_of_multiple_columns) with any applicable options), where the column grouping specifies all the column partitions except for the single-column partitions with system-determined format and AUTO COMPRESS.
  • COLUMN NO AUTO COMPRESS ALL BUT ((list_of_single_columns) or (list_of_multiple_columns) with any applicable options), where the column grouping specifies all the column partitions except for the single-column partitions with system-determined format and NO AUTO COMPRESS.

If none of the preceding cases apply, Vantage reports COLUMN ( (list_of_single_columns) or (list_of_multiple_columns) with any applicable options) that specifies all the column partitions for the COLUMN clause.

If none of the preceding cases apply, Vantage reports the shorter in length of the following for the COLUMN clause, where the grouping clause specifies all the column partitions:
  • COLUMN AUTO COMPRESS (list_of_single_columns) or (list_of_multiple_columns) or both with any applicable options)).
  • COLUMN NO AUTO COMPRESS (list_of_single_columns) or (list_of_multiple_columns) or both with any applicable options).

Derived Period Columns and SHOW TABLE

Derived period columns are output as PERIOD FOR (begin_column, end column). For a temporal table with a derived period VALIDTIME or TRANSACTIONTIME column, AS VALIDTIME or AS TRANSACTIONTIME follows the derived period column output, as appropriate. See Teradata Vantage™ - Temporal Table Support , B035-1182 and Teradata Vantage™ - ANSI Temporal Table Support , B035-1186 .