Information on indexes is in the system views SYSINDEX, SYSIQINDEX, SYSIXCOL, and for join indexes, SYSIQJOINIXTABLE. See Chapter 8, “System Views” in Reference: Building Blocks, Tables, and Procedures for a description of these views.
You can also use the stored procedure sp_iqindex to display a list of indexes and information about them. For example, to list the indexes in the Departments table, issue the command:
sp_iqindex 'Departments'
The following information displays. Output is displayed in two pieces for readability:
table_name |
table_owner |
column_name |
index_type |
|---|---|---|---|
Departments |
GROUPO |
DepartmentHeadID |
FP |
Departments |
GROUPO |
DepartmentHeadID |
HG |
Departments |
GROUPO |
DepartmentID |
FP |
Departments |
GROUPO |
DepartmentID |
HG |
Departments |
GROUPO |
DepartmentName |
FP |
index_name |
unique_index |
dbspace_id |
remarks |
|---|---|---|---|
ASIQ_IDX_T740_C3_FP |
N |
16,387 |
(NULL) |
ASIQ_IDX_T740_C3_HG |
N |
16,387 |
(NULL) |
ASIQ_IDX_T740_C1_FP |
U |
16,387 |
(NULL) |
ASIQ_IDX_T740_I4_HG |
Y |
16,387 |
(NULL) |
ASIQ_IDX_T740_C2_FP |
N |
16,387 |
(NULL) |
If you omit the table name from the command, sp_iqindex displays this information for all tables in the database.
