Standard Form of Display for SHOW TABLE
- SHOW TABLE 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; only those FORMAT specifications that differ from the defaults.
Therefore, 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.
Therefore, 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.
- 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.For Object File System tables, MERGEBLOCKRATIO has a default value that you cannot change.
- SHOW TABLE displays character constant strings, including TITLE, COMPRESS, and DEFAULT values, as follows:
Constant Type Value Display Format Printable single byte character Teradata Latin Other Internal Teradata hexadecimal - Typically, SHOW TABLE reports only column FORMAT attributes that do match the default format for the column.
The exception is columns of DATE data type, for which the system 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.
SHOW TABLE If DATABLOCKSIZE or FREESPACE Has Been Modified
If the DATABLOCKSIZE or FREESPACE (or both) of the table has been modified using the ALTER TABLE or CREATE TABLE statement, then SHOW TABLE reports the defined data block size or percent freespace (or both).
If the data block size or freespace value is not listed, the default value is in effect.
Applications of SHOW TABLE IN XML
The IN XML option for SHOW TABLE enables Teradata Tools and Utilities applications and third-party tools to retrieve and display table definitions and their properties in XML format.
- Displaying and modifying table definitions using a GUI that need not be able to understand and write DDL statements.
- Offline analysis of SQL workloads that require detailed definitions of the database objects being referenced.
- Easily moving tables from one database version to another using a Teradata Tools and Utilities program like Teradata Data Mover that can parse XML object definitions and produce DDL statements to apply on the target database server, with the mapping of database features to database releases available on the Teradata client.
SHOW TABLE Support for Global Temporary and Volatile Tables
When you specify the keyword TEMPORARY, the table definition for the materialized global temporary table in the current session is displayed; else the definition for the base global temporary table is displayed.
The keyword VOLATILE is reported when the requested table is a volatile table.
SHOW TABLE and Language Support Mode of a System
You cannot submit a SHOW TABLE request against a dictionary table to determine the language support mode for your system because Vantage returns the value Unicode as the response to such a request.
Instead, you must submit the following SELECT request to determine the language support mode used by your system.
SELECT infodata FROM DBC.dbcinfoV WHERE infokey='language support mode';
| System Language Support Mode | Returned InfoKey Value |
|---|---|
| Standard | Standard |
| Japanese | Japanese |