SQLTables - Get Table Information

Purpose

Specification:

DB2 CLI 2.1

ODBC 1.0

SQLTables() returns a list of table names and associated information stored in the system catalog 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 process a result set generated by a query.

Syntax

SQLRETURN   SQLTables        (
                SQLHSTMT          StatementHandle,   /* hstmt */
                SQLCHAR      FAR  *CatalogName,      /* szCatalogName */
                SQLSMALLINT       NameLength1,       /* cbCatalogName */
                SQLCHAR      FAR  *SchemaName,       /* szSchemaName */
                SQLSMALLINT       NameLength2,       /* cbSchemaName */
                SQLCHAR      FAR  *TableName,        /* szTableName */
                SQLSMALLINT       NameLength3,       /* cbTableName */
                SQLCHAR      FAR  *TableType,        /* szTableType */
                SQLSMALLINT       NameLength4);      /* cbTableType */

Function arguments

Table 89. SQLTables arguments

Data type	Argument	Use	Description
SQLHSTMT	`StatementHandle`	Input	Statement handle.
SQLCHAR	`CatalogName`	Input	Buffer that may contain a `pattern-value` to qualify the result set. `Catalog` is the first part of a 3 part table name. This field is ignored by DB2 Everyplace.
SQLSMALLINT	`NameLength1`	Input	Length of `CatalogName`. This field is ignored by DB2 Everyplace.
SQLCHAR	`SchemaName`	Input	Buffer that may contain a `pattern-value` to qualify the result set by schema name. This field is ignored by DB2 Everyplace.
SQLSMALLINT	`NameLength2`	Input	Length of `SchemaName`. This field is ignored by DB2 Everyplace.
SQLCHAR	`TableName`	Input	Buffer that may contain a `pattern-value` to qualify the result set by table name.
SQLSMALLINT	`NameLength3`	Input	Length of `TableName`.
SQLCHAR	`TableType`	Input	DB2 Everyplace only supports type TABLE. This field is ignored by DB2 Everyplace.
SQLSMALLINT	`NameLength4`	Input	This field is ignored by DB2 Everyplace.

Note that the TableName arguments accept search patterns.

Usage

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

Sometimes, an application calls SQLTables() with null pointers TableName argument so that no attempt is made to restrict the result set returned. For some data sources that contain a large quantity of tables, this scenario maps to an extremely large result set and very long retrieval times.

The result set returned by SQLTables() contains the columns listed in Table 90 in the order given. The rows are ordered by TABLE_NAME.

Calls to SQLTables() should be used sparingly, because in many cases they map to a complex and thus expensive query against the system catalog. The results should be saved rather than repeating calls.

The VARCHAR columns of the catalog functions result set have been declared with a maximum length attribute of 128 to be consistent with SQL92 limits. Since DB2 names are less than 128, the application can choose to always set aside 128 characters (plus the null-terminator) for the output buffer, or alternatively, call SQLGetInfo() with the SQL_MAX_TABLE_NAME_LEN to determine the actual lengths of the TABLE_NAME column supported by the connected DBMS.

Table 90. Columns Returned By SQLTables

Column Name	Data type	Description
TABLE_CAT	VARCHAR(128)	This is always a zero-length string.
TABLE_SCHEM	VARCHAR(128)	This is always a zero-length string.
TABLE_NAME	VARCHAR(128)	The name of the table.
TABLE_TYPE	VARCHAR(128)	Identifies the type given by the name in the TABLE_NAME column. It always has the string value 'TABLE'.
REMARKS	VARCHAR(254)	Contains the descriptive information about the table.

Return codes

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

Table 91. SQLTables SQLSTATEs

SQLSTATE	Description	Explanation
HY001	Memory allocation failure.	DB2 CLI is unable to allocate memory required to support execution or completion of the function.
HY014	No more handles.	DB2 CLI was unable to allocate a handle due to internal resources.
HY090	Invalid string or buffer length.	The value of one of the name length arguments was less than 0, but not equal to SQL_NTS. The valid of one of the name length arguments exceeded the maximum value supported for that data source. The maximum supported value can be obtained by calling the `SQLGetInfo()` function.

Restrictions

None.

Related reference

SQLGetInfo--Get general information