16.20 - Example: SHOW TABLE - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Content Type
Programming Reference
Publication ID
B035-1144-162K
Language
English (United States)
Last Update
2019-05-24

If a SHOW TABLE request is entered for a table that has been modified, Teradata Database returns the original CREATE TABLE SQL text, including all current modifications.

For example, this CREATE TABLE statement defines the table.

     CREATE TABLE personnel.emp_bonus (
       emp_no    SMALLINT FORMAT '9(5)' 
                 CHECK (emp_no BETWEEN 10001 AND 32001) NOT NULL, 
       bonus_no  SMALLINT FORMAT 'Z9' 
                 CHECK (bonus_no BETWEEN 0 and 99) NOT NULL, 
       bonus_amt DECIMAL (6,2) 
                 CHECK (bonus_amt BETWEEN 1.00 AND 5000.00))
      UNIQUE PRIMARY INDEX (emp_no);

Then, this ALTER TABLE statement subsequently modifies the emp_bonus table.

     ALTER TABLE emp_bonus
       ADD dept_no SMALLINT;

When you submit a SHOW TABLE request for the emp_bonus table, the system returns the following CREATE TABLE SQL text. The DeptNo column is included in the CREATE TABLE SQL text. The report also displays default specifications, such as FALLBACK.

     SHOW TABLE personnel.emp_bonus;
     CREATE SET TABLE personnel.emp_bonus, FALLBACK (
       emp_no SMALLINT FORMAT '9(5)' CHECK (emp_no BETWEEN 10001 
                                                   AND     32001) 
                                     NOT NULL, 
       bonus_no SMALLINT FORMAT 'Z9' CHECK (bonus_no BETWEEN 0 
                                                     AND    99) 
                                     NOT NULL,
       bonus_amt DECIMAL (6,2) CHECK (bonus_amt BETWEEN 1.00 
                                                AND  5000.00), 
       dept_no SMALLINT) 
     UNIQUE PRIMARY INDEX ( emp_no );