16.20 - View - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
view_name
The name of the view whose most recent SQL create text is to be reported. There is an upper limit of 12,500 characters that SHOW VIEW can display.

A SHOW VIEW IN XML request does not report all of the definition constructs for views, so it is not possible to decompose and reconstruct their definitions from their reported XML format definitions.

Despite this, the XML text for view definitions is helpful because it includes the following useful information.

  • The names and data types of the columns in the view definition.
  • A list of all of the referenced database objects in the view definition.

For further information, see Teradata Vantage™ SQL Data Definition Language Detailed Topics , B035-1184 .

Example: SHOW VIEW

This example begins with the creation of the view, then progresses to the SHOW VIEW request and finally presents the result of the report request.

     CREATE VIEW staff_info
      (number, name, position, department, sex, dob) AS
       SELECT employee.empno, name, jobtitle, deptno, sex, dob 
       FROM employee
       WHERE jobtitle NOT IN ('Vice Pres', 'Manager') 
       WITH CHECK OPTION;

When you submit the following SHOW VIEW request, Teradata Database returns the following view definition.

     SHOW VIEW staff_info;
     CREATE VIEW staff_info 
      (number, name, position, department, sex, dob) AS
     SELECT employee.empno, name, jobtitle, deptno, sex, dob 
     FROM employee
     WHERE jobtitle NOT IN ('Vice Pres', 'Manager') 
     WITH CHECK OPTION;

Example: SHOW VIEW in XML Format

This example returns the CREATE VIEW text for table user_name.v1 in XML format. Unlike a SHOW IN XML TABLE request, the XML document generated by a SHOW IN XML VIEW request does not report the parsed query for the view, and only contains the column definitions of the view and the database objects it references. As a result, this XML document cannot be used to recreate the view. However, it can be used to create a table having the same column definition of the original view.

The table that v1 is defined on looks like this.

     CREATE SET TABLE TESTDB.vt1, NO FALLBACK, NO BEFORE JOURNAL,
         NO AFTER JOURNAL, CHECKSUM = DEFAULT, DEFAULT MERGEBLOCKRATIO (
       a1 INTEGER NOT NULL,
       b1 INTEGER,
       c1 CHARACTER(20) CHARACTER SET UNICODE NOT CASESPECIFIC,
       d1 DECIMAL(15,2));

The create text for v1 looks like this.

     CREATE VIEW v1 AS
       SELECT a1, b1, c1
       FROM vt1
       WHERE d1 > 10000;

When you submit the following SHOW VIEW IN XML request, Teradata Database returns the report that follows the request.

     SHOW VIEW v1 IN XML;
<?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">
<View name="v1" dbName="testdb">
<ColumnList>
<Column name="a1" order="1">
<DataType><Integer/></DataType>
<Constraint nullable="false"/>
</Column>
<Column name="b1" order="2">
<DataType><Integer/></DataType>
</Column>
<Column name="c1" order="3">
<DataType>
<Char length="20" varying="false" uppercase="false" casespecific="false"
charset="unicode"/>
</DataType>
</Column>        
</ColumnList>
<RefList>
<Ref name="vt1" dbName="testdb" type="table"/>
</RefList>
<SQLText>
<![CDATA[
CREATE VIEW V1 AS
SELECT A1, B1, C1
FROM VT1
WHERE D1 > 10000;
</SQLText>
</View>
<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>