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
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 |
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. Use of SQL_ATTR_ESCAPE_CHAR will be deprecated in a future release. Support for the SQL_ATTR_ESCAPE_CHAR value is only honored if the connection attribute SQL_ATTR_OLD_MTADTA_BEHAVIOR is set to SQL_TRUE.
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.
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
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. |