15.10 - Table - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K
Display the most recent SQL create text. SHOW displays a standardized CREATE TABLE request that would create the named table with the following exceptions.
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:
  • If the AutoCompressDefault cost profile is set to 0, neither AUTO COMPRESS nor NO AUTO COMPRESS follows COLUMN in the report.
  • If the AutoCompressDefault cost profile is set to 1, AUTO COMPRESS follows COLUMN in the report.
  • If the AutoCompressDefault cost profile is set to 2, NO AUTO COMPRESS follows COLUMN in the report.

If all of the column partitions are single-column partitions with system-determined format and NO AUTO COMPRESS, and the value of the cost profile constant AutoCompressDefault is 1 or 2, Teradata Database 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, the following report variations depend on the value of the AutoCompressDefault cost profile constant:
  • If the value of the cost profile AutoCompressDefault is 0, Teradata Database reports the shorter in length between
COLUMN ALL BUT ((list_of_columns_in_the_multicolumn_partition )) or 
COLUMN (list_of_columns_in_the_single-column_partitions ) for the COLUMN clause
  • If the value of the cost profile AutoCompressDefault is 1 or 2, Teradata Database 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 the value of the cost profile constant AutoCompressDefault is 1 or 2, 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, Teradata Database 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, the value of the cost profile AutoCompressDefault is 0, and at least one, but not all, of the column partitions are single-column partitions with system-determined format and AUTO COMPRESS, Teradata Database 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.

If none of the preceding cases apply, the value of the cost profile AutoCompressDefault is 1 or 2, and at least one, but not all, of the column partitions are single-column partitions with system-determined format, Teradata Database reports the shorter in length of the following 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 and the value of the cost profile AutoCompressDefault is 0, Teradata Database 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.

If none of the preceding cases apply and the value of the cost profile AutoCompressDefault is 1 or 2, Teradata Database reports the shorter in length of the following for the COLUMN clause, where the grouping clause specifies all the column partitions:
  • 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).

For more information about the AutoCompressDefault cost profile, see SQL Request and Transaction Processing, B035-1142.

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.

Because a standard display form is used, the result might not be identical to the text used to create the table. Its form 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.

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 Temporal Table Support.

table_name
Name of the table.
TEMPORARY
The table for which the most recent SQL create text is to be reported is a materialized global temporary table.
If you specify TEMPORARY, then table_name must specify a global temporary table.
If you do not specify TEMPORARY, and the table is a global temporary table, the base global temporary table is shown.

Example: SHOW TABLE

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, consider the following requests used to create and subsequently modify the emp_bonus 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);
     ALTER TABLE emp_bonus
       ADD dept_no SMALLINT;

When you submit a SHOW TABLE request for the emp_bonus table, for example, Teradata Database returns the following CREATE TABLE SQL text.

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

The DeptNo column is included in the CREATE TABLE SQL text. The report also displays default specifications, such as FALLBACK.

Example: SHOW TABLE with Column-Level Named Constraints

The request in this example names constraints at the column level.

     CREATE TABLE good_1 (
       column_1 INTEGER NOT NULL CONSTRAINT primary_1 PRIMARY KEY, 
       column_2 INTEGER NOT NULL CONSTRAINT unique_1 UNIQUE, 
       column_3 INTEGER CONSTRAINT check_1 CHECK (column_3 > 0),
       column_4 INTEGER CONSTRAINT reference_1 REFERENCES parent_1);

Now perform the following SHOW TABLE request on good_1.

     SHOW TABLE good_1;

The request returns the following CREATE TABLE SQL text.

     CREATE SET TABLE personnel.good_1, NO FALLBACK,
        NO BEFORE JOURNAL, NO AFTER JOURNAL (
        column_1 INTEGER NOT NULL,
        column_2 INTEGER NOT NULL,
        column_3 INTEGER,
        column_4 INTEGER,
        CONSTRAINT check_1 CHECK ( column_3 > 0 ),
        CONSTRAINT reference_1 FOREIGN KEY ( column_4 )
           REFERENCES personnel.parent_1 ( f2 ))
     UNIQUE PRIMARY INDEX primary_1 ( column_1 )
     UNIQUE INDEX unique_1 ( column_2 );

Example: SHOW TABLE with REFERENCES Constraint

Table t1 is created with a referential constraint which specifies that FOREIGN KEY columns (f1 ) reference the unique primary index columns in table good_1.

     CREATE TABLE t1 (
       f1 INTEGER NOT NULL
       f2 INTEGER)
     PRIMARY INDEX (f1)
     FOREIGN KEY (f1) REFERENCES good_1;

A SHOW TABLE table_name on t1 returns the following CREATE TABLE SQL text.

     SHOW TABLE t1;
     CREATE SET TABLE t1, NO FALLBACK,
        NO BEFORE JOURNAL, NO AFTER JOURNAL (
     f1 INTEGER NOT NULL,
     f2 INTEGER)
     PRIMARY INDEX ( f1 )
     FOREIGN KEY ( f1 ) REFERENCES personnel.good_1( column_1 );

Example: SHOW TABLE for no Specified MERGEBLOCKRATIO Option

If you do not specify the MERGEBLOCKRATIO option when you create a table, Teradata Database returns the SQL create text for the table as if you had specified the DEFAULT MERGEBLOCKRATIO option. For example, suppose you create a table named emp_table , but do not specify anything for the MERGEBLOCKRATIO option. Then you submit a SHOW TABLE request for emp_table like this.

     SHOW TABLE emp_table;
            *** Text of DDL statement returned.
            *** Total elapsed time was 1 second.
     CREATE SET TABLE emp_table, NO FALLBACK, NO BEFORE JOURNAL,
           NO AFTER JOURNAL,CHECKSUM = DEFAULT, DEFAULTMERGEBLOCKRATIO (
              emp_no INTEGER )
     PRIMARY INDEX ( emp_no );

Example: SHOW TABLE

This CREATE TABLE request creates the following table for a user whose default character type is Latin.

     CREATE TABLE kanji_user.table_1,
       NO FALLBACK,
       NO BEFORE JOURNAL,
       NO AFTER JOURNAL
       (
        clatin     CHARACTER(5),
        ckanji1    CHARACTER(5) CHARACTER SET UNICODE,
        cgraphic   CHARACTER(5) CHARACTER SET GRAPHIC,
        ckanjisjis CHARACTER(5) CHARACTER SET KANJISJIS,
        cunicode   CHARACTER(5))
     PRIMARY INDEX (clatin);

A SHOW TABLE request for this table returns the following CREATE TABLE SQL text, as expected.

     CREATE TABLE kanji_user.table_1,
       NO FALLBACK,
       NO BEFORE JOURNAL,
       NO AFTER JOURNAL (
        clatin CHARACTER(5) CHARACTER SET LATIN,
        ckanji1 CHARACTER(5) CHARACTER SET UNICODE,
        cgraphic CHARACTER(5) CHARACTER SET GRAPHIC,
        ckanjisjis CHARACTER(5) CHARACTER SET KANJISJIS,
        cunicode CHARACTER(5) CHARACTER SET LATIN)
     PRIMARY INDEX ( clatin );

Example: SHOW TABLE for Algorithmic Compression

Assume that you have defined algorithmic compression on a set of table columns. The SHOW TABLE output for such a table returns the names of the UDFs that contain the compression and decompression algorithms.

Assume table t1 is created with the following definition.

     CREATE TABLE t1 (
       col_1 INTEGER, 
       col_2 CHARACTER(10) COMPRESS ALGCOMPRESS scsu_comp
                           ALGDECOMPRESS scsu_decomp ('abc', 'efg'));

You then submit a SHOW TABLE request on t1. Teradata Database returns the following SQL create text for this request.

CREATE SET TABLE user_name.t1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
col_1 INTEGER,
col_2 CHARACTER(10) 
CHARACTER SET LATIN NOT CASESPECIFIC   
COMPRESS
ALGCOMPRESS scsu_comp
ALGDECOMPRESS scsu_decomp ('abc','efg'))
PRIMARY INDEX ( col_1 );

Assume you create table t2 with the following definition.

     CREATE TABLE t2 (
       col_1 INTEGER, 
       col_2 CHARACTER(10));

You then submit the following ALTER TABLE request on t2 to modify its definition to include algorithmic compression on col_2.

     ALTER TABLE t2
     ADD col_2 CHARACTER(10) COMPRESS ALGCOMPRESS scsu_comp 
                             ALGDECOMPRESS scsu_decomp ('abc', 'efg');

A SHOW TABLE request for table t2 returns the following SQL create text.

     CREATE SET TABLE user_name.t2, NO FALLBACK,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
          col_1 INTEGER,
          col_2 CHARACTER(10) 
             CHARACTER SET LATIN NOT CASESPECIFIC   
             COMPRESS
             ALGCOMPRESS scsu_comp
             ALGDECOMPRESS scsu_decomp ('abc       ','efg       '))
          PRIMARY INDEX ( col_1 );

Example: SHOW TABLE for a Column-Partitioned Table or Join Index

The following rules apply to the report returned by a SHOW TABLE request made on a column-partitioned table or a SHOW JOIN INDEX request made on a column-partitioned join index.

For a partitioned table or join index, Teradata Database includes an ADD option for a partitioning level if the level has column partitioning or if the number of defined partitions for a row partitioning level is less than the maximum number of partitions for the level and the level is not the first level that has row partitioning. The output is the same as for a row-partitioned table or join index if the object only has row partitioning and an ADD option was not specified for the level and the number of row partitions currently defined is the same as the maximum number of row partitions for the level.

The rules for the ADD option are as follows.

  • ADD 0 is never reported for any row partitioning level.
  • ADD is never reported for the first row partitioning level.
  • ADD is always reported for a row partitioning level if the number of defined partitions is less than the maximum number of partitions and it is not the first row partitioning level.
  • ADD is always reported for a column partitioning level even if it specifies ADD 0.

If ADD is reported, the value is equal to the maximum number of partitions minus the currently defined number of partitions for that partitioning level.

For a column-partitioned table or join index, the output is the same except the PARTITION BY clause includes a COLUMN clause. Grouping is included in the COLUMN clause, not in the column definition list for a table or the select list for a join index. The column grouping in the COLUMN clause is reported as follows.

If all the column partitions are single-column partitions with system-determined COLUMN or ROW format and without a NO AUTO COMPRESS specification, Teradata Database does not report a column grouping following the COLUMN clause.

If all the column partitions are single-column partitions except for 1 that is multicolumn, and all column partitions have system-determined COLUMN or ROW format without a NO AUTO COMPRESS specification, Teradata Database reports the shorter in terms of characters of these bullet items following the COLUMN clause.

  • COLUMN ALL BUT ((multicolumn_partition_column_list_separated_by_commas_and_ordered_by_field_ID ))

    or

  • COLUMN (columns_in_single-column_partitions_separated_by_commas_
and_ordered_by_field ID )

For example,

     COLUMN ALL BUT ((d, p, z))
     COLUMN (a, b, c, g)

If the partitioning is not that of the previous bullet, and at least one, but not all, of the column partitions are single column partitions with system-determined COLUMN or ROW format without the NO AUTO COMPRESS option that specifies all the column partitions except for the single-column partitions with system-determined COLUMN or ROW format without the NO AUTO COMPRESS option is included following the COLUMN clause.

Teradata Database reports the different forms with entries separated by commas and ordered by field ID.

  • COLUMN ALL BUT (single_columns_list_with_options )

    or

  • COLUMN ALL BUT (multiple_columns_list_with_options )

    or

  • COLUMN ALL BUT (single_columns_list_with_options , (multiple_columns_list_
with_options))

For example,

     COLUMN ALL BUT (ROW d, (i, t), k NO AUTO COMPRESS, 
                     COLUMN(m, s, u, v))
     COLUMN ALL BUT (COLUMN(j, m, o))
     COLUMN ALL BUT (ROW(e, j, z) NO AUTO COMPRESS)

Otherwise, if none of the preceding cases, Teradata Database reports one of the following forms with entries separated by commas and ordered by the field ID of the first column of each column partition. Within a column partition, Teradata Database orders the entries by the field IDs of the partition columns.

  • COLUMN (single_columns_list_with_options )

    or

  • COLUMN (multiple_columns_list _with_options )

    or

  • COLUMN (single_columns_list_with_options, (multiple_columns_list_with_
options ))

For example,

     COLUMN (ROW d, (i, t), k NO AUTO COMPRESS, COLUMN(m, s, u, v))

This example creates column-partitioned table t1 with the following definition. When t1 was created, the value of the AutoCompressDefault cost profile constant was 1.

     CREATE TABLE t1 (
       a INTEGER, 
       b INTEGER, 
       c INTEGER, 
       d INTEGER, 
       e INTEGER, 
       f INTEGER, 
       g INTEGER, 
       h INTEGER,
       i INTEGER, 
       j INTEGER, 
       k INTEGER, 
       l INTEGER, 
       m INTEGER, 
       n INTEGER, 
       o INTEGER, 
       p INTEGER, 
       q INTEGER, 
       r INTEGER,
       s INTEGER,
       t INTEGER, 
       u INTEGER, 
       v INTEGER, 
       w INTEGER, 
       x INTEGER, 
       y INTEGER, 
       z INTEGER)
     NO PRIMARY INDEX 
     PARTITION BY COLUMN ALL BUT (a, b, (g, d), ROW(s, t, j), 
                                  h NO AUTO COMPRESS, x) ADD 65509;
      *** Table has been created.
      *** Total elapsed time was 2 seconds.

Assume that the value of AutoCompressDefault has changed to 2. A SHOW TABLE request for t1 returns the following information.

     SHOW TABLE t1;
      *** Text of DDL statement returned. 
      *** Total elapsed time was 1 second.
-----------------------------------------------------------------------
CREATE MULTISET TABLE user_name.t1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
a INTEGER,
b INTEGER,
c INTEGER,
d INTEGER,
e INTEGER,
f INTEGER,
g INTEGER,
h INTEGER,
i INTEGER,
j INTEGER,
k INTEGER,
l INTEGER,
m INTEGER,
n INTEGER,
o INTEGER,
p INTEGER,
q INTEGER,
r INTEGER,
s INTEGER,
t INTEGER,
u INTEGER,
v INTEGER,
w INTEGER,
x INTEGER,
y INTEGER,
z INTEGER)
NO PRIMARY INDEX
PARTITION BY COLUMN AUTO COMPRESS
  ALL BUT ((d, g), h NO AUTO COMPRESS, ROW(j, s, t)) ADD 65509;

Columns a, b, and x are not included in the grouping clause for the COLUMN clause because they are single-column partitions by default with system-determined format and autocompression. The column partitions and columns for a column partition are in the same order as in the definition list.

Example: SHOW TABLE in XML Format

This example returns the CREATE TABLE text for table user_name.t1 in XML format. As is true for a SHOW IN XML HASH INDEX request, the XML document generated by a SHOW IN XML TABLE request reports all of the information required to recreate the table.

The table is defined as follows.

     CREATE SET TABLE user_name.t1, NO FALLBACK, NO BEFORE JOURNAL,
      NO AFTER JOURNAL, CHECKSUM=DEFAULT, DEFAULT MERGEBLOCKRATIO (
       a INTEGER NOT NULL,
       b CHARACTER(20) CHARACTER SET UNICODE NOT CASESPECIFIC,
       c DATE FORMAT 'YY/MM/DD',
     PRIMARY KEY (a));

A SHOW TABLE IN XML request for table t1 returns the following XML document for its create text.

     SHOW IN XML TABLE t1;
<?xml version="1.0" encoding="utf-8"?>
<TeradataDBObjectSet xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://schemas.teradata.com/TeradataDBObject TeradataDBObject.xsd"
xmlns="http://schemas.teradata.com/TeradataDBObject">
<Table kind="set" name="t1" dbName="user_name
"
fallback="false" beforeJournal="no" afterJournal="no" checkSumLevel="default" mergeBlockRatio="default">
<ColumnList>
<Column name="a" order="1">
		    <DataType><Integer/></DataType>
<Constraint nullable="false"/>
</Column>
<Column name="b" order="2">
<DataType>
<Char length="20" varying="false" uppercase="false" casespecific="false" charset="unicode"/>
</DataType>
</Column>
<Column name="c" order="3" format="YY/MM/DD">
<DataType><Date/></DataType>
</Column>
</ColumnList>
<TableConstraint>
<PrimaryKey>
<ColumnList>
<Column name="a" order="1"/>
</ColumnList>
</PrimaryKey>
</TableConstraint>
<SQLText>
<![CDATA[
CREATE SET TABLE user_name.t1, 
NO FALLBACK,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
a INTEGER NOT NULL,
b CHAR(20) CHARACTER SET UNICODE NOT CASESPECIFIC,
c DATE FORMAT 'YY/MM/DD',
PRIMARY KEY ( a )) 
;
            
</SQLText>
</Table>
   <Environment>
     <Server dbRelease="14.10.00.00" dbVersion="14.10.00.06"
hostName="testhost"/>
     <User userId="0" userName="dbc"/>
     <Session charset="utf8" dateTime="2012-01-01T14:00:00-08:00"/>
   </Environment>
</TeradataDBObjectSet>

Example: SHOW TABLE with a Derived Period Column

This is an example of SHOW TABLE output for the following table definition which includes a derived period column.

CREATE TABLE employee (
	eid INTEGER NOT NULL,
	name VARCHAR(100) NOT NULL,
	deptno INTEGER NOT NULL,
	jobstart DATE NOT NULL,
	jobend DATE NOT NULL,
	PERIOD FOR jobduration (jobstart, jobend)
) PRIMARY INDEX(eid);

Below is the SHOW TABLE output.

CREATE SET TABLE TEST.employee ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
eid INTEGER NOT NULL,
name VARCHAR(100) NOT NULL,
deptno INTEGER NOT NULL,
jobstart DATE NOT NULL,
jobend DATE NOT NULL,
PERIOD FOR jobduration (jobstart, jobend)
) PRIMARY INDEX(eid);