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.
- 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.
- 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 .