SHOW TABLE Examples | Teradata Vantage - Example: SHOW TABLE - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

If a SHOW TABLE request is entered for a table that has been modified, Vantage 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 );