Index information in system views

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.

Displaying indexes using stored procedures

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.