SQLTables - Get table information

 

SQLTables() returns a list of table names and associated information stored in the system catalogs of the connected data source. The list of table names is returned as a result set, which can be retrieved using the same functions that are used to retrieve a result set generated by a SELECT statement.

 

Syntax

SQLRETURN SQLTables  (SQLHSTMT       hstmt,
                      SQLCHAR        *szCatalogName,
                      SQLSMALLINT    cbCatalogName,
                      SQLCHAR        *szSchemaName,
                      SQLSMALLINT    cbSchemaName,
                      SQLCHAR        *szTableName,
                      SQLSMALLINT    cbTableName,
                      SQLCHAR        *szTableType,
                      SQLSMALLINT    cbTableType);

 

Function arguments

Table 1. SQLTables arguments

Data type	Argument	Use	Description
SQLHSTMT	hstmt	Input	Statement handle.
SQLCHAR *	szCatalogName	Input	Buffer that might contain a pattern-value to qualify the result set. Catalog is the first part of a three-part table name. This must be a NULL pointer or a zero length string.
SQLSMALLINT	cbCatalogName	Input	Length of szCatalogName. This must be set to 0.
SQLCHAR *	szSchemaName	Input	Buffer that might contain a pattern-value to qualify the result set by schema name.
SQLSMALLINT	cbSchemaName	Input	Length of szSchemaName.
SQLCHAR *	szTableName	Input	Buffer that might contain a pattern-value to qualify the result set by table name.
SQLSMALLINT	cbTableName	Input	Length of szTableName.
SQLCHAR *	szTableType	Input	Buffer that might contain a value list to qualify the result set by table type. The value list is a list of values separated by commas for the types of interest. Valid table type identifiers might include: ALL, ALIAS, BASE TABLE, MATERIALIZED QUERY TABLE, SYSTEM TABLE, TABLE, VIEW. If szTableType argument is a NULL pointer or a zero length string, then this is equivalent to specifying all of the possibilities for the table type identifier. If SYSTEM TABLE is specified, then both system tables and system views (if there are any) are returned. The table types can be specified with or without quotation marks.
SQLSMALLINT	cbTableType	Input	Size of szTableType

The szCatalogName, szSchemaName, and szTableName arguments accept search patterns.

An escape character can be specified in conjunction with a wildcard character to allow that actual character to be used in the search pattern. The escape character is specified on the SQL_ATTR_ESCAPE_CHAR environment attribute.

 

Usage

Table information is returned in a result set where each table is represented by one row of the result set.

To support obtaining just a list of schemas, the following special semantics for the szSchemaName argument can be applied: if szSchemaName is a string containing a single percent (%) character, and cbCatalogName, szTableName, and szTableType are empty strings, then the result set contains a list of non-duplicate schemas in the data source. The result set returned by SQLTables() contains the columns listed in the following table in the order given.

Table 2. Columns returned by SQLTables

Column number/name	Data type	Description
1 TABLE_CAT	VARCHAR(128)	The current server.
2 TABLE_SCHEM	VARCHAR(128)	The name of the schema containing TABLE_NAME.
3 TABLE_NAME	VARCHAR(128)	The name of the table, view, alias, or synonym.
4 TABLE_TYPE	VARCHAR(128)	This identifies the type given by the name in the TABLE_NAME column. It can have the string values ALIAS, BASE TABLE, MATERIALIZED QUERY TABLE, SYSTEM TABLE, TABLE, or VIEW.
5 REMARKS	VARCHAR(254)	This contains the descriptive information about the table.

 

Return codes

• SQL_SUCCESS

• SQL_SUCCESS_WITH_INFO

• SQL_ERROR

• SQL_INVALID_HANDLE

 

Diagnostics

Table 3. SQLTables SQLSTATEs

SQLSTATE	Description	Explanation
24000	Cursor state that is not valid	Cursor-related information is requested, but no cursor is open.
40003 *	Statement completion unknown	The communication link between the CLI and the data source fails before the function completes processing.
HY001	Memory allocation failure	The driver is unable to allocate memory required to support the processing or completion of the function.
HY009	Argument or buffer length that is not valid	The value of one of the name length arguments is less than 0, but not equal to SQL_NTS.
HY021	Internal descriptor that is not valid	The internal descriptor cannot be addressed or allocated, or it contains a value that is not valid.
HYC00	Driver not capable	The catalog part (the first part) of a three-part table name is not supported by the data source.

 

Parent topic:

DB2 UDB CLI functions