15.10 - View - 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
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 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>