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

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Advanced SQL Engine
Teradata Database
Release Number
Release Date
September 2020
Content Type
Programming Reference
Publication ID
English (United States)

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 );