SelectFromObjectList | Application Programming Reference | Teradata Vantage - 17.10 - SelectFromObjectList - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Application Programming Reference

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1090-171K
Language
English (United States)

Displays the database and table objects contained within a specified object list.

Syntax

REPLACE PROCEDURE TDSTATS.SelectFromObjectList (
  IN ObjectListName   VARCHAR(128) CHARACTER SET UNICODE,
  IN ObjectListId     BIGINT,
  IN ResolveWildCards CHAR(1) CHARACTER SET LATIN
)
  ...
;

Syntax Elements

ObjectListName
Name of the existing object list.
If you specify NULL, all object lists in the TDSTATS database are displayed.
ObjectListId
ID of an existing object list.
ResolveWildCards
Possible values:
  • Y. If you specify Y, object names containing wildcard characters will be expanded.
  • N. If you specify N, wildcard characters within object names will be retained. This is the default value.

Output

Column Description
ObjectListName Name of object list containing the object name. For more information, see the ObjectName output parameter.
DatabaseName Database whose objects are contained in the object list. For more information, see the ObjectListName output parameter.
ObjectName Table contained within the object list. If you specify NULL, all tables in the DatabaseName output parameter are contained in the list. For more information, see the ObjectListName or DatabaseName output parameters.

Usage Notes

You can view object list data in the TDSTATS.ObjectList and TDSTATS.ObjectListEntry tables. The names of the object lists are stored in the TDSTATS.ObjectList.ListName column.

Returning Result Set

The output of this external stored procedure is in the form of a stored procedure dynamic result. That is, the external stored procedure can return result sets to the client application or to the caller of the external stored procedure (in addition to consuming the result sets itself) upon completion of the external stored procedure.

For more information about stored procedure dynamic result sets, see Teradata Vantageā„¢ - SQL External Routine Programming, B035-1147.

Displaying Statistics

To display ... You must specify ...
the objects for a specified list either a value for ObjectListName or ObjectListId.
all lists stored in the TDSTATS database NULL for ObjectListName and ObjectListId.

Example: Using SelectFromObjectList

The following example shows how to display all objects in the list named MyObjects.

CALL TDSTATS.SelectFromObjectList('MyObjects',NULL,'Y');
 *** Query completed. 4 rows found. 1 column returned.
 *** Total elapsed time was 1 second.
ObjectListName  DatabaseName  ObjectName
--------------  ------------  ----------
MyObjects       db1           ?
MyObjects       db2           tx
MyObjects       db3           tx
MyObjects       db3           ty
MyObjects       db3           tz