Lists information about indexes.
sp_iqindex [table_name='tablename' ],
[column_name='columnname' ],[table_owner='tableowner' ]
sp_iqindex_alt [table_name='tablename' ],
[column_name='columnname' ],[table_owner='tableowner' ]
DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.
Parameter |
Description |
|---|---|
Syntax1 |
If you do not specify either of the first two parameters, but specify the next parameter in the sequence, you must substitute NULL for the omitted parameters. For example, sp_iqindex NULL,NULL,DBA and sp_iqindex Departments,NULL,DBA. |
Syntax2 |
You can specify the parameters in any order. Enclose them in single quotes. |
Syntax 3 and 4 |
Produces slightly different output when a multicolumn index is present. Allows the same options as Syntax 1 and 2. |
Displays information about indexes in the database. Specifying one of the parameters returns the indexes from only that table, column, or tables owned by the specified user. Specifying more than one parameter filters the results by all of the parameters specified. Specifying no parameters returns all indexes for all tables in the database.
Column name |
Description |
|---|---|
table_name |
The name of the table |
table_owner |
The owner of the table |
column_name |
The name of the column; multiple names can appear in a multicolumn index |
index_type |
The abbreviated index type (for example, HG, LF) |
index_name |
The name of the index |
unique_index |
'U' indicates the index is a unique index; otherwise, 'N' |
location |
TEMP = IQ temporary store, MAIN = IQ store, SYSTEM = catalog store |
remarks |
User comments added with the COMMENT statement |
sp_iqindex always produces one line per index. sp_iqindex_alt produces one line per index per column if there is a multicolumn index.