Tables Schema Collection

Tables schema collection returns information about TABLE and or VIEW objects by accessing Teradata database Data Dictionary views. It will access DBC.TABLES[V] or DBC.TABLES[V]X Data Dictionary view based on UseXViews Connection setting.

Column Name	Data Type	Description
TABLE_CATALOG	String	This field is always set to EMPTY.
TABLE_SCHEMA	String	Returns the name of the DATABASE in which TABLE or VIEW object resides.
TABLE_NAME	String	Returns the name of the TABLE or VIEW object.
TABLE_TYPE	String	Returns: TABLE to designate a TABLE object. SYSTEM TABLE to designate a TABLE object in DBC database. VIEW to designate a VIEW object. SYSTEM VIEW to designate a VIEW in DBC database.
REMARKS	String	Returns user-supplied text or commentary on the TABLE or VIEW object.
DATE_CREATED	DateTime	Returns the date and time the TABLE or VIEW was created.
DATE_MODIFIED	DateTime	Returns the date and time the TABLE or VIEW was last updated.
TABLE_TYPE_EX	String	Returns: QUEUE for QUEUE TABLE object. GLOBAL TEMPORARY, ON COMMIT PRESERVE ROWS for Global Temporary TABLE object with On Commit Preserve Rows option. GLOBAL TEMPORARY, ON COMMIT PRESERVE ROWS, VALIDTIME TEMPORAL for Global Temporary ValidTime Temporal TABLE object with On Commit Preserve Rows option. GLOBAL TEMPORARY, ON COMMIT PRESERVE ROWS, TRANSACTIONTIME TEMPORAL for Global Temporary TransactionTime Temporal TABLE object with On Commit Preserve Rows option. GLOBAL TEMPORARY, ON COMMIT PRESERVE ROWS, BI-TEMPORAL for Global Temporary Bi-Temporal TABLE object with On Commit Preserve Rows option. GLOBAL TEMPORARY, ON COMMIT PRESERVE ROWS, NO PRIMARY INDEX for Global Temporary TABLE object with On Commit Preserve Rows and No Primary Index options. GLOBAL TEMPORARY, ON COMMIT PRESERVE ROWS, TIME SERIES for Global Temporary Time Series TABLE object with On Commit Preserve Rows option. GLOBAL TEMPORARY, ON COMMIT DELETE ROWS for Global Temporary TABLE object with On Commit Delete Rows option. GLOBAL TEMPORARY, ON COMMIT DELETE ROWS, VALIDTIME TEMPORAL for Global Temporary ValidTime Temporal TABLE object with On Commit Delete Rows option. GLOBAL TEMPORARY, ON COMMIT DELETE ROWS, TRANSACTIONTIME TEMPORAL for Global Temporary TransactionTime Temporal TABLE object with On Commit Delete Rows option. GLOBAL TEMPORARY, ON COMMIT DELETE ROWS, BI-TEMPORAL for Global Temporary Bi-Temporal TABLE object with On Commit Delete Rows option. GLOBAL TEMPORARY, ON COMMIT DELETE ROWS, NO PRIMARY INDEX for Global Temporary TABLE object with On Commit Delete Rows and No Primary Index options. GLOBAL TEMPORARY, ON COMMIT DELETE ROWS, TIME SERIES for Global Temporary Time Series TABLE object with On Commit Delete Rows option. NO PRIMARY INDEX for TABLE object with No Primary Index option. VALIDTIME TEMPORAL for TABLE object containing a ValidTime Temporal column. TRANSACTIONTIME TEMPORAL for TABLE object containing a TransactionTime Temporal column. BI-TEMPORAL for TABLE object containing both TransactionTime and ValidTime Temporal columns. TIME SERIES for TABLE object containing a generated System Time column. DIRECT ACCESS FOREIGN for Direct Access Foreign TABLE object. MATERIALIZED FOREIGN for Materialized Foreign TABLE object. NULL

Tables schema collection can be filtered or restricted to:

Restriction Column Name	Restriction Number	Description
TABLE_SCHEMA	1	This restriction can be set to a valid DATABASE object name.
TABLE_NAME	2	This restriction can be set to a valid TABLE or VIEW object name.
TABLE_TYPE	3	This restriction can be set to: T for TABLE Objects V for VIEW Objects String.Empty for TABLE and VIEW Objects

Restriction Column Name

Restriction Number

Description

TABLE_SCHEMA

This restriction can be set to a valid DATABASE object name.

TABLE_NAME

This restriction can be set to a valid TABLE or VIEW object name.

TABLE_TYPE

This restriction can be set to:

T for TABLE Objects
V for VIEW Objects
String.Empty for TABLE and VIEW Objects

Tables schema collection rows are ordered by:

Column Name	Description
TABLE_TYPE	Ascending sort order. That is SYSTEM TABLE, SYSTEM VIEW, TABLE and finally followed by VIEW.
TABLE_SCHEMA	Ascending case sensitive sort order. For example A through Z followed by a through z.
TABLE_NAME	Ascending case sensitive sort order.