Example: SHOW in XML Format - Advanced SQL Engine - Teradata Database

The SHOW IN XML statement returns the definitions of database objects referenced either directly or indirectly by a DML request in XML format. All database object names are qualified.

The XML result includes the definitions for all of the parent tables of the tables referenced in the DML request as well as the definitions for all of the base tables and base views for the views referenced in the DML request.

The definition for table t1 is as follows.

     CREATE SET TABLE user.t1, NO FALLBACK, NO BEFORE JOURNAL,
                              NO AFTER JOURNAL, CHECKSUM = DEFAULT,
                              DEFAULT MERGEBLOCKRATIO (
       a1 INTEGER NOT NULL,
       b1 CHAR(20) CHARACTER SET UNICODE NOT CASESPECIFIC,
       c1 DATE FORMAT 'YY/MM/DD',
     PRIMARY KEY (a1));

The definition for table t2 is as follows.

     CREATE SET TABLE user.t2, NO FALLBACK, NO BEFORE JOURNAL,
                              NO AFTER JOURNAL, CHECKSUM = DEFAULT,
                              DEFAULT MERGEBLOCKRATIO (
       a2 INTEGER NOT NULL,
       b2 DECIMAL(6,4) NOT NULL,
       c2 VARCHAR(20) CHARACTER SET UNICODE NOT CASESPECIFIC)
     UNIQUE PRIMARY INDEX (a2);

The request whose database object definitions are to be displayed is the following.

     SHOW SELECT a1, b1, b2 FROM t1, t2 WHERE a1=a2;
XML Document for SHOW IN XML SELECT a1, b1, b2 FROM t1, t2 WHERE a1=a2:
1.	<?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" version="1.0">
<Table objId="0:331" objVer="1" kind="set" baseClass="Table" name="t1" dbName="testdb" fallback="false" beforeJournal="no" afterJournal="no" checkSumLevel="default" mergeBlockRatio="default">
<ColumnList>
<Column name="a1" order="1">
<DataType><Integer/></DataType>
<Constraint nullable="false"/>
</Column>
<Column name="b1" order="2">
<DataType><Char length="20" varying="false" uppercase="false" casespecific="false" charset="unicode"/></DataType>
</Column>
<Column name="c1" order="3" format="YY/MM/DD">
<DataType><Date/></DataType>
</Column>
</ColumnList>
<TableConstraint>
<PrimaryKey>
<ColumnList>
<Column name="a1" order="1"/>
</ColumnList>
</PrimaryKey>
</TableConstraint>
<SQLText>
<![CDATA[
CREATE SET TABLE user.t1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
a1 INTEGER NOT NULL,
b1 CHAR(20) CHARACTER SET UNICODE NOT CASESPECIFIC,
c1 DATE FORMAT 'YY/MM/DD',
PRIMARY KEY (a1));
</SQLText>
</Table>
<Table objVer="1" objId="0:422" kind="set" baseClass="Table" name="t2" dbName="testdb" fallback="false" beforeJournal="no" afterJournal="no" checkSumLevel="default" mergeBlockRatio="default">
<ColumnList>
<Column name="a2" order="1">
<DataType><Integer/></DataType>
<Constraint nullable="false"/>
</Column>
<Column name="b2" order="2">
<DataType><Decimal precision="6" scale="4"/></DataType>
<Constraint nullable="false"/>
</Column>
<Column name="c2" order="3">
<DataType><Char length="20" varying="true" uppercase="false" casespecific="false" charset="unicode"/></DataType>
</Column>
</ColumnList>
<Index>
<PrimaryIndex unique="true">
<ColumnList>
<Column name="a2" order="1"/>
</ColumnList>
</PrimaryIndex>
</Index>
<SQLText>
<![CDATA[
CREATE SET TABLE TESTDB2.t2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
a2 INTEGER NOT NULL,
b2 DECIMAL(6,4) NOT NULL,
c2 VARCHAR(20) CHARACTER SET UNICODE NOT CASESPECIFIC)
UNIQUE PRIMARY INDEX ( a2 );
</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>