`SQLTables()` - Get table information

SQLTables() returns a list of table names and associated information that is stored in the system catalog of the connected data source. The list of table names is returned as a result set. You can retrieve this result set with the same functions that process a result set generated by a query.

ODBC specifications for `SQLTables()`

Table 1. `SQLTables()` specifications
ODBC specification level	In X/Open CLI CAE specification?	In ISO CLI specification?
1.0	Yes	No

Syntax

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

Function arguments

The following table lists the data type, use, and description for each argument in this function.

Table 2. `SQLTables()` arguments
Data type	Argument	Use	Description
SQLHSTMT	hstmt	input	Statement handle.
SQLCHAR *	szCatalogName	input	Buffer that can 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	The length, in bytes, of szCatalogName. This must be set to 0.
SQLCHAR *	szSchemaName	input	Buffer that can contain a pattern-value to qualify the result set by schema name.
SQLSMALLINT	cbSchemaName	input	The length, in bytes, of szSchemaName.
SQLCHAR *	szTableName	input	Buffer that can contain a pattern-value to qualify the result set by table name.
SQLSMALLINT	cbTableName	input	The length, in bytes, of szTableName.
SQLCHAR *	szTableType	input	Buffer that can contain a value list to qualify the result set by table type. The value list is a list of uppercase comma-separated single quoted values for the table types of interest. Valid table type identifiers can include: TABLE, VIEW, SYSTEM TABLE, ALIAS, SYNONYM, GLOBAL TEMPORARY TABLE, AUXILIARY TABLE, MATERIALIZED QUERY TABLE, or ACCEL-ONLY TABLE. If SYSTEM TABLE is specified, then both system tables and system views (if any) are returned.
SQLSMALLINT	cbTableType	input	Size of szTableType

Note that the szCatalogName, szSchemaName, and szTableName 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. To determine the type of access permitted on any given table in the list, the application can call SQLTablePrivileges(). Otherwise, the application must be able to handle a situation where the user selects a table for which SELECT privileges are not granted.

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 szCatalogName and szTableName are empty strings, then the result set contains a list of valid schemas in the data source.

If szTableType is a single percent character (%) and szCatalogName, szSchemaName, and szTableName are empty strings, then the result set contains a list of valid table types for the data source. (All columns except the TABLE_TYPE column contain null values.)

If szTableType is not an empty string, it must contain a list of uppercase, comma-separated values for the types of interest; each value can be enclosed in single quotes or without single quotes. For example, "'TABLE','VIEW'" or "TABLE,VIEW". If the data source does not support or does not recognize a specified table type, nothing is returned for that type.

If an application calls SQLTables() with null pointers for some or all of the szSchemaName, szTableName, and szTableType arguments, SQLTables() does not restrict the result set that is returned. For some data sources that contain a large number of objects, large result sets are returned, with very long retrieval times. You can reduce the result set size and retrieval time by specifying initialization keywords SCHEMALIST, SYSSCHEMA, or TABLETYPE in the Db2 ODBC initialization file. Those initialization keywords restrict the result set when SQLTables() supplies null pointers for szSchemaName and szTableType. If SQLTables() does not supply a null pointer for szSchemaName or szTableType, the associated keyword specification in the Db2 ODBC initialization file is not used.

The result set returned by SQLTables() contains the columns listed in Table 3 in the order given. The rows are ordered by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, and TABLE_NAME.

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

The VARCHAR columns of the catalog functions result set are declared with a maximum length attribute of 128 bytes to be consistent with ANSI/ISO SQL standard of 1992 limits. BecauseDB2 names are less than 128 bytes, the application can choose to always set aside 128 bytes (plus the nul-terminator) for the output buffer. Alternatively, you can call SQLGetInfo() with the InfoType argument set to each of the following values:

SQL_MAX_CATALOG_NAME_LEN, to determine the length of TABLE_CAT columns that the connected database management system supports
SQL_MAX_SCHEMA_NAME_LEN, to determine the length of TABLE_SCHEM columns that the connected database management system supports
SQL_MAX_TABLE_NAME_LEN, to determine the length of TABLE_NAME columns that the connected database management system supports
SQL_MAX_COLUMN_NAME_LEN, to determine the length of COLUMN_NAME columns that the connected database management system supports

Although new columns might be added and the names of the existing columns changed in future releases, the position of the current columns remains unchanged. The following table lists the columns in the result set SQLTables() currently returns.

Table 3. Columns returned by `SQLTables()`
Column Name	Data type	Description
TABLE_CAT	VARCHAR(128)	The name of the catalog containing TABLE_SCHEM. This column contains a null value.
TABLE_SCHEM	VARCHAR(128)	The name of the schema containing TABLE_NAME.
TABLE_NAME	VARCHAR(128)	The name of the table, or view, or alias, or synonym.
TABLE_TYPE	VARCHAR(128)	Identifies the type of object in the TABLE_NAME column. TABLE_TYPE can have one of the string values 'TABLE', 'VIEW', 'INOPERATIVE VIEW', 'SYSTEM TABLE', 'ALIAS', 'SYNONYM', 'GLOBAL TEMPORARY TABLE', 'AUXILIARY TABLE', 'MATERIALIZED QUERY TABLE', or 'ACCEL-ONLY TABLE'. 'ACCEL-ONLY TABLE' is an extended table type, and is returned only if initialization keyword EXTENDEDTABLEINFO is set to 1.
REMARKS	VARCHAR(762)	Contains the descriptive information about the table.
TEMPORAL_TABLE_TYPE	VARCHAR(11)	Contains the type of temporal table. Possible values are: SYSTEM System-period temporal table. APPLICATION Application-period temporal table. BITEMPORAL Bitemporal table. Empty string Not a temporal table. The result set contains this column only if initialization keyword EXTENDEDTABLEINFO is set to 1.
IS_ACCELERATED	VARCHAR(3)	Indicates whether the table is an accelerated table. Possible values are YES or NO. The result set contains this column only if initialization keyword EXTENDEDTABLEINFO is set to 1.
ACCEL_ARCHIVE_STATUS	CHAR(1)	Contains the archive status of the table in the accelerator database. See the description of the ARCHIVE column in SYSACCEL.SYSACCELERATEDTABLES table for the possible values and their meanings. The result set contains this column only if initialization keyword EXTENDEDTABLEINFO is set to 1.
IS_ARCHIVE_ENABLED	VARCHAR(3)	Indicates whether the table is an archive-enabled table. Possible values are YES or NO. The result set contains this column only if initialization keyword EXTENDEDTABLEINFO is set to 1.

Return codes

After you call SQLTables(), it returns one of the following values:

SQL_SUCCESS
SQL_SUCCESS_WITH_INFO
SQL_ERROR
SQL_INVALID_HANDLE

Diagnostics

The following table lists each SQLSTATE that this function generates, with a description and explanation for each value.

Table 4. `SQLTables()` SQLSTATEs
SQLSTATE	Description	Explanation
08S01	Communication link failure.	The communication link between the application and data source fails before the function completes.
24000	Invalid cursor state.	A cursor is open on the statement handle.
HY001	Memory allocation failure.	Db2 ODBC is not able to allocate the required memory to support the execution or the completion of the function.
HY010	Function sequence error.	The function is called during a data-at-execute operation. (That is, the function is called during a procedure that uses the `SQLParamData()` or `SQLPutData()` functions.)
HY014	No more handles.	Db2 ODBC is not able to allocate a handle due to low internal resources.
HY090	Invalid string or buffer length.	This SQLSTATE is returned for one or more of the following reasons: The value of one of the name length arguments is less than 0, but not equal to SQL_NTS. The value of one of the name length arguments exceeds the maximum value supported for that data source. You can obtain this maximum value with `SQLGetInfo()`.
HYC00	Driver not capable.	Db2 ODBC does not support catalog as a qualifier for table name.

Example

The following example shows an application that uses SQLTables() to generate a result set of table name information that matches a search pattern. For another example, see Functions for querying environment and data source information.

Figure 1. An application that returns a result set of table name information

/* ... */
SQLRETURN init_tables(SQLHDBC hdbc )
{
    SQLHSTMT        hstmt;
    SQLRETURN       rc;
    SQLUSMALLINT     rowstat[MAX_TABLES];
    SQLUINTEGER      pcrow;
    rc = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
    /* SQL_ROWSET_SIZE sets the max number of result rows to fetch each time */
    rc = SQLSetStmtAttr(hstmt, SQL_ATTR_ROWSET_SIZE, (void*)MAX_TABLES, 0);
    /* Set size of one row, used for row-wise binding only */
    rc = SQLSetStmtAttr(hstmt, SQL_ATTR_BIND_TYPE, 
                        (void *)sizeof(table) / MAX_TABLES, 0);
    printf("Enter Search Pattern for Table Schema Name:\n");
    gets(table->schem);
    printf("Enter Search Pattern for Table Name:\n");
    gets(table->name);
    rc = SQLTables(hstmt, NULL, 0, table->schem, SQL_NTS,
                   table->name, SQL_NTS, NULL, 0);
    rc = SQLBindCol(hstmt, 2, SQL_C_CHAR, (SQLPOINTER) &table->schem, 129,
                    &table->schem_l);
    rc = SQLBindCol(hstmt, 3, SQL_C_CHAR, (SQLPOINTER) &table->name, 129,
                    &table->name_l);
    rc = SQLBindCol(hstmt, 4, SQL_C_CHAR, (SQLPOINTER) &table->type, 129,
                    &table->type_l);
    rc = SQLBindCol(hstmt, 5, SQL_C_CHAR, (SQLPOINTER) &table->remarks, 255,
                    &table->remarks_l);
    /* Now fetch the result set */
/* ... */

SQLTables() - Get table information

ODBC specifications for SQLTables()