Example: SHOW TABLE with an Auto Column - 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™

Following is the table definition for this example, which specifies the AUTO COLUMN option for column c.

CREATE TABLE MyTable (
      a INTEGER,
      b JSON(16776192) INLINE LENGTH 4096 CHARACTER SET LATIN,
      c JSON(16776192) INLINE LENGTH 4096 CHARACTER SET LATIN AUTO COLUMN)
PRIMARY INDEX ( a );

This statement displays information for the table MyTable.

SHOW TABLE MyTable;

Below is the SHOW TABLE output.

CREATE SET TABLE TEST.MyTable ,FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      a INTEGER,
      b JSON(16776192) INLINE LENGTH 4096 CHARACTER SET LATIN,
      c JSON(16776192) INLINE LENGTH 4096 CHARACTER SET LATIN AUTO COLUMN)
PRIMARY INDEX ( a );

This statement displays the table information in XML format.

SHOW IN XML TABLE MyTable;

Below is the SHOW TABLE output in XML format.

<?xml version="1.0" encoding="UTF-16" standalone="no" ?><TeradataDBObjectSet version="1.0" 
xmlns="http://schemas.teradata.com/dbobject" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.teradata.com/dbobject http://schemas.teradata.com/dbobject/DBObject.xsd">
<Table afterJournal="No" 
baseClass="Table" 
beforeJournal="No" 
checkSumLevel="Default" 
dbName="TEST" 
fallback="false" 
kind="Set" 
map="TD_MAP1" map_kind="contiguous" 
mergeBlockRatio="Default" 
name="MyTable" objId="0:3654" objVer="1" systemVersioned="false"> 

<ColumnList>
<Column name="a" nullable="true" order="1"> 
<DataType><Integer/></DataType> 
</Column> 

<Column name="b" nullable="true" order="2"> 
<DataType><JSON charset="LATIN" inlinelength="4096" size="16776192"/> 
</DataType> 
</Column> 

<Column autocolumn="true" name="c" nullable="true" order="3"> 
<DataType><JSON charset="LATIN" inlinelength="4096" size="16776192"/> 
</DataType> 
</Column> 
</ColumnList>

<Indexes><PrimaryIndex unique="false">
<ColumnList>
<Column name="a" order="1"/></ColumnList> 
</PrimaryIndex>
</Indexes> 
<SQLText><![CDATA[CREATE SET TABLE TEST.MyTable ,NO
FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL,
     CHECKSUM = DEFAULT,
     DEFAULT MERGEBLOCKRATIO,
     MAP = TD_MAP1
     (
      a INTEGER,
      b JSON(16776192) INLINE LENGTH 4096 CHARACTER SET LATIN,
      c JSON(16776192) INLINE LENGTH 4096 CHARACTER SET LATIN AUTO COLUMN)
PRIMARY INDEX ( a )]]> 
</SQLText> 
</Table> 

<Environment><Server dbRelease="16.50d.00.07" 
dbVersion="16.50d.00.07dr183550" hostName="localhost"/> 
<User userId="00000100" userName="DBC"/> 
<Session charset="ASCII" 
dateTime="2017-09-21T16:41:44"/> 
</Environment> 
</TeradataDBObjectSet>