17.10 - SHOW object Syntax Elements - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)
IN XML
Returns the report in XML format.

This option only applies to SHOW HASH INDEX, SHOW JOIN INDEX, SHOW TABLE, and SHOW VIEW. You can also report on error tables by using the SHOW IN XML TABLE error_table_name syntax.

For information on the usage limitations of the output of the IN XML option for join indexes and views, see join_index_name or view_name, respectively, later in this table.

The XML schema for the output produced by this option is maintained in:

http://schemas.teradata.com/queryplan/queryplan.xsd
database_name
The name of the containing database for the object.

For UDTs, methods, and UDFs related to UDTs, this is always SYSUDTLIB.

user_name
The name of the containing user for the object.

For UDTs, methods, and UDFs related to UDTs, this is always SYSUDTLIB.

hash_index_name
The name of the hash index whose most recent SQL create text is to be reported.
join_index_name

The name of the join index whose most recent SQL create text is to be reported.

If the join index is defined on a temporal table, the SQL text returned by SHOW JOIN INDEX includes the temporal qualifier that was associated with the embedded SELECT request at the time the join index was created, whether it was explicitly specified or implicitly derived from the default session temporal qualifier.

If the join index is system-defined, the SQL text returned by SHOW JOIN INDEX displays the non-reserved keyword SYSTEM_DEFINED between the words CREATE and JOIN.

If you submit this SQL text as a CREATE JOIN INDEX request, the database returns an error to the requestor because SYSTEM_DEFINED is not valid SQL text.

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

However, the XML text for join index definitions is helpful because it includes the following useful information:
  • The names and data types of the columns in the join index definition.
  • A list of all of the referenced database objects in the join index definition.

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

For a column-partitioned join index, the SQL text returned by SHOW JOIN INDEX includes a PARTITION BY clause with a COLUMN clause. Grouping, if any, is included in the COLUMN clause, not in the select expression list.

See table_name later in this table for a list of exceptions to the rules for what the database reports for a SHOW JOIN INDEX request. These rules apply equally to SHOW JOIN INDEX and SHOW TABLE requests.

macro_name
The name of the macro whose most recent SQL create text is to be reported.

There is an upper limit of 12,500 characters that SHOW MACRO can display.

table_name
Display the most recent SQL create text. See SHOW TABLE.
data_table_name
The name of the data table for which the error table whose most recent SQL create text is to be reported.

This syntax is useful if you did not define an explicit name for the error table and you do not know the system-assigned default name assigned to it.

error_table_name
The name of the error table whose most recent SQL create text is to be reported.
trigger_name
The name of the trigger whose most recent SQL create text is to be reported. There is an upper limit of 12,500 characters that SHOW TRIGGER can display.
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.

procedure_name
The name of the SQL procedure whose most recent SQL create text is to be reported.

SHOW PROCEDURE displays procedure text as it was sent to the database. You must type line breaks in the procedure using the Enter or Return key to properly display the text.

specific_function_name
The specific function name for the UDF whose most recent SQL create text is to be reported.
function_name
The function name for the UDF whose most recent SQL create text is to be reported.
data_type
UDT_name
The data type parameters, including UDTs, that uniquely identify an overloaded function name. For a list of data types, see Data Types Syntax.
specific_method_name
The specific name for the method whose most recent SQL create text is to be reported.
method_name
The name of the method whose most recent SQL create text is to be reported.
UDT_name
The name of the UDT for which the most recent SQL create text, including that for its CREATE ORDERING and CREATE TRANSFORM statements, are to be reported. This specification applies to both the SHOW CAST and the SHOW TYPE statements.

Similarly, if the only cast or ordering or transform functionality for a UDT is system-generated, the system does not report that DDL because it was not created by SQL CREATE requests, so there is no create text to return.

ARRAY_name
VARRAY_name
The name of the ARRAY or VARRAY type for which the most recent SQL create text is to be reported. The SQL create text is reported in standard Teradata syntax using the ARRAY keyword.

The ordering and transform functionality for the ARRAY type are not displayed because they are system-generated, so there is no create text to return.

storage_format
Storage format of the DATASET type for which to show the schema.
SYSUDTLIB
Name of the target database containing the schema.
schema_name
Name of schema.
uif_name
Name of user-installed file.
Display the text associated with the installed file and display the DDL used to install the file.
Binary user-installed files (UIF) are not displayed.
uif_database_name
Name of database containing the user-installed file (UIF).
uif_user_name
Name of user containing the user-installed file (UIF).
constraint_name
The name of the constraint for which the most recent SQL create text is to be reported.
authorization_name
The name used to create the authorization object with a CREATE AUTHORIZATION request.
GLOP_set_name
The name of the GLOP set whose definition is to be reported.

The request reports the DDL for the CREATE GLOP SET request that created GLOP_set_name.