IN XML Option for SHOW Requests - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

You can export the definitions of tables or hash indexes from Vantage in XML format. Teradata provides the XML schemas required to interpret the XML files produced by this feature.

This enables the following applications.
  • Displaying and altering table and hash index definitions using a GUI that need not be able to understand and write SQL DDL statements.
  • Performing offline analyses of SQL workloads that require detailed definitions of the tables or hash indexes being referenced.
  • Moving table and hash index definitions easily from one database version to another using a Teradata Tools and Utilities program like Teradata Data Mover that can parse XML object definitions and produce DDL requests to apply on the target database server, with the mapping of database features to database releases available on the Teradata client.

Teradata Tools and Utilities applications and third party tools can retrieve, display, and modify table and hash index definitions in XML format.

Teradata Tools and Utilities applications and third party tools can retrieve and display the XML formatted text for view and join index column definitions, but because the IN XML option does not report all of the definition constructs for views and join indexes, it is not possible to decompose and reconstruct their definitions from their XML format definitions.

The XML output for the SHOW IN XML VIEW and SHOW IN XML JOIN INDEX statements also contains a list in XML format of all the views and tables the view references, and all the tables the join index references.

The following SHOW database_object_name statements have the IN XML option, allowing you to produce reports in XML format in addition to their default SQL DDL code.
  • SHOW HASH INDEX
  • SHOW JOIN INDEX
  • SHOW TABLE
  • SHOW VIEW

You can also report the most recent create text for error tables in XML format if you use the SHOW IN XML TABLE error_table_name syntax.

Of these statements, only the XML output for SHOW IN XML HASH INDEX and SHOW IN XML TABLE statements contains complete information about the object definitions.

Because the output of statements using the IN XML option is in XML format, you must use a tool with a standard XML parser to interpret that output. Also be aware that unlike the non-XML forms of these SHOW statements, the statements that specify the IN XML option return their responses in a single result set for most cases. For multistatement requests that contain multiple SHOW IN XML statements, Vantage returns multiple result sets, with each result set containing one XML document that corresponds to the matching request.