SQL QUERY INDEXES Statement

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

The SQL QUERY INDEXES statement returns a result set that is a list of statistics about a single table and the indexes associated with the table. 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 table-indexes 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 table-indexes query. This value must have been returned by a successful SQL CONNECT DATASOURCE statement.

TableName (input). This argument must specify a nonnumeric value. The value identifies the table name of the table for which the index descriptions are to be returned. Table names available from a data source can be obtained with the SQL QUERY TABLES statement. This argument specifies the table name literally, that is, the value is not a string search pattern. For letters in this string, case is significant.

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 for which the index 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 for which the index 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. SchemaName cannot contain a string search pattern. 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.

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.

Besides information on indexes, the result set includes a row with table statistics. The table statistics include the cardinality (number of rows in the table) and the number of pages used to store the table. Some data sources cannot return this information and provide NULL values in the table statistics row. Also, for some data sources, the cardinality is approximate. The meaning of "pages" is data source dependent.

After the SQL QUERY INDEXES statement is executed, SQL FETCH ROW statements can be executed to fetch the index description rows. Each row describes one index or statistic of the specified table. The result set is ordered by NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_NAME, and ORDINAL_POSITION (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 index or statistic 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 table-indexes query from the beginning, if desired. The SQL QUERY INDEXES statement starts the table-indexes query, so an initial SQL START QUERY statement is not required.

The SQL END QUERY statement can be used to end the table-indexes query.

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

A successful SQL QUERY INDEXES 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.)

The columns in the result set for an table-indexes query are as follows:

#	Column Name (1)	Data Type (2)	Comments
1	TABLE_CAT (TABLE_QUALIFIER)	Varchar	Catalog name of the table to which the statistic or index applies; 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 of the table to which the statistic or index applies; 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 not NULL	Table name of the table to which the statistic or index applies.
4	NON_UNIQUE	Smallint	Indicates whether the index prohibits duplicate values: sql-True if the index values may be nonunique. sql-False if the index values must be unique. NULL is returned if TYPE is sql-Table-Stat.
5	INDEX_QUALIFIER	Varchar not NULL	The identifier that is used to qualify the index name when doing a DROP INDEX; NULL is returned if an index qualifier is not supported by the data source or if TYPE is sql-Table-Stat. If a non-null value is returned in this column, it must be used to qualify the index name on a DROP INDEX statement; otherwise the TABLE_SCHEM should be used to qualify the index name.
6	INDEX_NAME	Varchar	Index name; NULL is returned if TYPE is sql-Table-Stat.
7	TYPE	Smallint not NULL	Type of information being returned: sql-Table-Stat indicates a statistic for the table (in the CARDINALITY or PAGES column). sql-Index-Clustered indicates a clustered index. sql-Index-Hashed indicates a hashed index. sql-Index-Other indicates another type of index.
8	ORDINAL_POSITION (SEQ_IN_INDEX)	Smallint	Column sequence number in index (starting with 1); NULL is returned if TYPE is sql-Table-Stat.
9	COLUMN_NAME	Varchar	Column name. If the column is based on an expression, such as SALARY + BENEFITS, the expression is returned; if the expression cannot be determined, an empty string is returned. NULL is returned if TYPE is sql-Table-Stat.
10	ASC_OR_DESC (COLLATION)	Char(1)	Sort sequence for the column; "A" for ascending; "D" for descending; NULL is returned if column sort sequence is not supported by the data source or if TYPE is sql-Table-Stat.
11	CARDINALITY	Integer	Cardinality of table or index; number of rows in table if TYPE is sql-Table-Stat; number of unique values in the index if TYPE is not sql-Table-Stat; NULL is returned if the value is not available from the data source.
12	PAGES	Integer	Number of pages used to store the index or table; number of pages for the table if TYPE is sql-Table-Stat; number of pages for the index if TYPE is not sql-Table-Stat; NULL is returned if the value is not available from the data source, or if not applicable to the data source.
13	FILTER_CONDITION	Varchar	If the index is a filtered index, this is the filter condition, such as SALARY > 30000; if the filter condition cannot be determined, this is an empty string. NULL if the index is not a filtered index, it cannot be determined whether the index is a filtered index, or TYPE is sql-Table-Stat.

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 an table-indexes 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, TABLE_NAME, and COLUMN_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, sql-ConMaxTableNameLength, and sql-ConMaxColumnNameLength, 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 to obtain the sql-QryNoCols data item and comparing its value to 13. 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.

If the row in the result set corresponds to a table statistic, the driver sets the TYPE column to sql-Table-Stat and sets the NON_UNIQUE, INDEX_QUALIFIER, INDEX_NAME, ORDINAL_POSITION, COLUMN_NAME, and ASC_OR_DESC columns to NULL. If CARDINALITY or PAGES are not available from the data source, the driver sets them to NULL.

SQL QUERY INDEXES Statement Examples:

           SQL QUERY INDEXES
               sql-QueryHandle,
               sql-ConnectionHandle,
               sql-TableName.

           SQL QUERY INDEXES
               sql-QueryHandle,
               sql-ConnectionHandle,
               sql-TableName,
               "Northwind",     *> catalog (qualifier) name
               "dbo".           *> schema (owner) name