Catalogue Command (ODBC Only)
Returns meta data about indexes used with a tables.
indexes DATABASE SCHEMA TABLE UNIQUE
DATABASE
Catalog/database name.
SCHEMA
Schema name or Table Owner.
TABLE
Table name of the table to be processed.
UNIQUE [ALL | UNIQUE]
An indicate to whether only unique indexes or all indexes are to be returned .
Description
Use this command to obtain index information a table. The application must have been logged into a data source using the login command, otherwise the following error message will be displayed:
Error: Not connected to database
Certain database management systems do not support different "Database" (or Catalogue) or "Schema" (or Table Owner), if so, a null string needs to be passed as the first and/or second parameter using the syntax '' (i.e. two single quotes).
If the parameters are valid, a result set will be returned containing one row as there must be only a single key column that connects two tables.The row contains a number of fields, the actual number may increase, dependent of the ODBC version, but will always start with the following:
Column Name |
Column Number |
Comment |
TABLE_CAT |
1 |
Table catalog name; NULL if not applicable to the data source. |
TABLE_SCHEM |
2 |
Table schema name; NULL if not applicable to the data source. |
TABLE_NAME |
3 |
Table name. |
NON_UNIQUE |
4 |
Indicates whether the index prohibits duplicate values: SQL_TRUE (i.e. 1) if the index values can be nonunique. SQL_FALSE (i.e. 0) if the index values must be unique. NULL is returned if TYPE is SQL_TABLE_STAT. |
INDEX_QUALIFIER |
5 |
The identifier that is used to qualify the index name 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. |
INDEX_NAME |
6 |
Index name; NULL is returned if TYPE is SQL_TABLE_STAT. |
TYPE |
7 |
Type of information being returned: SQL_TABLE_STAT indicates a statistic for the table (in the CARDINALITY or PAGES column). SQL_INDEX_BTREE indicates a B-Tree index. SQL_INDEX_CLUSTERED (i.e. 1) indicates a clustered index. SQL_INDEX_CONTENT indicates a content index. SQL_INDEX_HASHED (i.e. 2) indicates a hashed index. SQL_INDEX_OTHER (i.e. 3) indicates another type of index. |
ORDINAL_POSITION |
8 |
Column sequence number in index (starting with 1); NULL is returned if TYPE is SQL_TABLE_STAT. |
COLUMN_NAME |
9 |
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. |
ASC_OR_DESC |
10 |
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. |
CARDINALITY |
11 |
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. |
PAGES |
12 |
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. |
FILTER_CONDITION |
13 |
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.. |
If the parameters are invalid, then an error message "ERROR: Failed to obtain index information" will be returned.
Example
subroutine GetUniqueIndexes(string: TableOwner_SV, string: Table_SV, string: IndexList_SV) is
string Column_LSV
copy "" to IndexList_SV
begin guarded
response to start
send "indexes '' " TableOwner_SV " " Table1_SV " UNIQUE\n" to SQL
response to "SQL>" from SQL
leave block
response to line from SQL
extract from input
skip by "~" # Catalog
skip by "~" # Schema
skip by "~" # Table
skip by "~" # Unique
skip by "~" # Index Qualifier
skip by "~" # Index Name
skip by "~" # Type
skip by "~" # Ordinal Position
take to "~" Column_LSV
append Column_LSV "\n" to IndexList_SV
end
