SQL QUERY TABLES Statement

< > Browse Statements Query Statements Connection Statements Example Flow Chart Table of Contents

The SQL QUERY TABLES statement returns a result set that lists and describes the tables in the data source identified by an InstantSQL connection handle. The result set is identified by the query handle value that is returned.

QueryHandle (output). This argument must refer to a numeric integer data item with at least six digits of precision. The argument specifies the data item where the query handle value that identifies the tables query is to be stored.

ConnectionHandle (input). This argument must specify a numeric integer value with at least six digits of precision. The value identifies the connection to be used for the tables query. This value must have been returned by a successful SQL CONNECT DATASOURCE statement.

CatalogName (input). This argument must refer to a nonnumeric value. The value specifies the catalog name (or, for ODBC v2, the qualifier name) of the table or tables for which the table descriptions are to be returned. This argument is optional. The default is no catalog name constraint on the query results. For letters in this string, case is significant. CatalogName cannot contain a string search pattern. If a driver supports catalogs for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string (all spaces) denotes those tables that do not have catalogs. This argument must be omitted (or specified as OMITTED) if the data source does not support catalog names.

SchemaName (input). This argument must refer to a nonnumeric value. The value specifies the schema name (or, for ODBC v2, the owner name) of the table or tables for which the table descriptions are to be returned. This argument is optional. The default is no schema name constraint on the query results. If a driver supports schemas for some tables but not for others, such as when the driver retrieves data from different DBMSs, an empty string (all spaces) denotes those tables that do not have schemas. This argument identifies the schema name or names using a string search pattern. (See the topic InstantSQL Search Patterns for information on search patterns.) For letters in this string, case is significant. If the driver does not support schema names, this argument must be omitted (or specified as OMITTED); otherwise, an ODBC error may occur with SQLSTATE S1C00, optional feature not implemented.

TableName (input). This argument must refer to a nonnumeric value. The value specifies the table name or names for which the table descriptions are to be returned. This argument is optional. The default is to return all tables in the data source that meet the constraints specified by the other arguments. This argument identifies the table name or names using a string search pattern. (See the topic InstantSQL Search Patterns for information on search patterns.) For letters in this string, case is significant.

TableType (input). This argument must refer to a nonnumeric value. The value specifies a comma separated list of table type values for which the table descriptions are to be returned. The individual table type values may be enclosed in single quotation (') marks or unquoted. For example, “'TABLE','VIEW'” or “TABLE, VIEW”. This argument is optional. The default is configurable; see TableType in the topic InstantSQL Configuration for information on configuring the default. Valid table type values are 'TABLE', 'VIEW', 'SYSTEM TABLE', 'GLOBAL TEMPORARY', 'LOCAL TEMPORARY', 'ALIAS', and 'SYNONYM'. The meanings of 'ALIAS' and 'SYNONYM' are driver-specific. If the data source does not support a specified table type, the SQL QUERY TABLES statement does not return any results for that type. The table type values should always be specified in upper case. The table type value for each table is returned in the TABLE_TYPE column of the result set, which is described below.

Valid query handle values returned by InstantSQL are in the range 1 through 9999. The application program may use the value zero in a query handle data item to indicate that the query has not been created or has been dropped.

After the SQL QUERY TABLES statement is executed, SQL FETCH ROW statements can be executed to fetch the table description rows. Each row describes one table of the specified connection. The result set is ordered by TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, and TABLE_NAME (see result set description below for an explanation of these result column names).

The SQL GET DATA statement can be used to transfer the data that describes the table or the SQL BIND COLUMN statement can be used so that the data is transferred into COBOL data items as part of the SQL FETCH ROW statement.

The SQL START QUERY statement can be used with the returned query handle value to re-start the tables query from the beginning, if desired. The SQL QUERY TABLES statement starts the tables query, so an initial SQL START QUERY statement is not required.

The SQL END QUERY statement can be used to end the tables query.

A successful SQL QUERY TABLES statement sets the type of the query identified by the value of the QueryHandle argument to sql-QryTables. (The type of a query can be obtained using the SQL DESCRIBE QUERY statement.)

A successful SQL QUERY TABLES statement sets the status of the query identified by the value of the QueryHandle argument to sql-StatExecuting. (The current status of a query can be obtained using the SQL DESCRIBE QUERY statement.)

Some drivers may return rows for tables that are not accessible by the user. Drivers are required to provide information on whether this is the case. The sql-ConAccessibleTables data item in the extended connection description group returned by an SQL DESCRIBE CONNECTION statement contains this information.

To support enumeration of catalogs, schemas, and table types, the following special semantics are defined for the CatalogName, SchemaName, TableName, and TableType arguments of SQL QUERY TABLES:

If CatalogName is "%" and SchemaName and TableName are empty strings (all spaces, for example " "), then the result set contains a list of valid catalogs for the data source. (All columns except the TABLE_CAT column contain NULLs.)
If SchemaName is "%" and CatalogName and TableName are empty strings (all spaces, for example " "), then the result set contains a list of valid schemas for the data source. (All columns except the TABLE_SCHEM column contain NULLs.)
If TableType is "%" and CatalogName, SchemaName and TableName are empty strings (all spaces, for example " "), then the result set contains a list of valid table types for the data source. (All columns except the TABLE_TYPE column contain NULLs.)

The columns in the result set for a tables query are as follows:

#	Column Name (1)	Data Type (2)	Comments
1	TABLE_CAT (TABLE_QUALIFIER)	Varchar	Catalog name; NULL if not applicable to the data source. If a driver supports catalogs for some tables but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those tables that do not have catalogs.
2	TABLE_SCHEM (TABLE_OWNER)	Varchar	Schema name; NULL if not applicable to the data source. If a driver supports schemas for some tables but not for others, such as when the driver retrieves data from different DBMSs, it returns an empty string ("") for those tables that do not have schemas.
3	TABLE_NAME	Varchar	Table name.
4	TABLE_TYPE	Varchar	Table type name; one of the following: "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM", or a data source–specific type name. The meanings of "ALIAS" and "SYNONYM" are driver-specific.
5	REMARKS	Varchar	A description of the table.

Note 1 The first column names listed are those returned by ODBC drivers that conform to ODBC version 3.0 and later. ODBC drivers that conform to prior versions return the column names listed second in parentheses when the column name is different. Because of these version issues, getting or binding data for a tables query should use column numbers rather than column names. (The version of ODBC supported by the driver can be obtained in sql-ConDriverODBCVersion by using the SQL DESCRIBE CONNECTION statement with an extended connection description group.)

Note 2 The lengths of VARCHAR columns for the result set are not shown because the actual lengths depend on the data source. To determine the actual lengths of the TABLE_CAT, TABLE_SCHEM, and TABLE_NAME columns, an application can use the SQL DESCRIBE CONNECTION statement with an extended connection description group. The maximum lengths of the indicated columns will then be available in sql-ConMaxCatalogNameLength, sql-ConMaxSchemaNameLength, and sql-ConMaxTableNameLength, respectively. Also, the SQL DESCRIBE COLUMN statement can be used to obtain the maximum length of any column in the result set.

Some drivers may return additional driver-specific columns. The existence of such columns can be determined using the SQL DESCRIBE QUERY statement to obtain the sql-QryNoCols data item and comparing its value to 5. The descriptions of such columns can be obtained with the SQL DESCRIBE COLUMN statement. Column numbers relative to the end of the result set should be used to access driver-specific columns.

SQL QUERY TABLES Statement Examples:

           SQL QUERY TABLES
               sql-QueryHandle,
               sql-ConnectionHandle.

           SQL QUERY TABLES
               sql-QueryHandle,
               sql-ConnectionHandle,
               "%", " ", " ".         *> list catalogs

           SQL QUERY TABLES
               sql-QueryHandle,
               sql-ConnectionHandle,
               " ", "%", " ".         *> list schemas

          SQL QUERY TABLES
               sql-QueryHandle,
               sql-ConnectionHandle,
               " ", " ", " ", "%".    *> list table types

           SQL QUERY TABLES
               sql-QueryHandle,
               sql-ConnectionHandle,
               "Northwind",      *> catalog (qualifier) name
               "dbo",            *> schema (owner) name
               "Cust%",          *> table names beginning with "Cust"
               "'TABLE','VIEW'". *> table type list

           SQL QUERY TABLES
               sql-QueryHandle,
               sql-ConnectionHandle,
               OMITTED,          *> catalog (qualifier) name
               OMITTED,          *> schema (owner) name
               OMITTED,          *> table name pattern
               "SYSTEM TABLE".   *> table type list