sp_iqindex and sp_iqindex_alt procedures

Function

Lists indexes and information about them.

Syntax 1

sp_iqindex ( [ table_name ],[column_name ],[table_owner ] )

Syntax 2

sp_iqindex [table_name='tablename' ],
[column_name='columnname' ],[table_owner='tableowner' ]

Syntax 3

sp_iqindex_alt ( [ table_name ],[column_name ],[table_owner ] )

Syntax 4

sp_iqindex_alt [table_name='tablename' ],
[column_name='columnname' ],[table_owner='tableowner' ]

Usage

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 parameter(s). For example, sp_iqindex NULL,NULL,DBA and sp_iqindex department,NULL,DBA.

Syntax2 The parameters can be specified in any order. Be sure to 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.

**Table 9-13: sp_iqindex and sp_iqindex_alt columns**
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 Temp Store, MAIN = IQ Store, LOCAL = IQ Local Store, SYSTEM = Catalog Store
remarks	User comments added with the COMMENT statement

The sp_iqindex format always produces one line per index. The sp_iqindex_alt format produces one line per index per column if there is a multicolumn index.

Examples

Example 4

The following variations in syntax both return all indexes on columns with the name dept_id:

call sp_iqindex (NULL,'dept_id')

sp_iqindex column_name='dept_id'

table_ name	table_ owner	column_ name	index_ type	index_name	unique_ index	location	remarks
department	DBA	dept_id	FP	ASIQ_IDX_T201_C1_FP	N	Main	(NULL)
department	DBA	dept_id	HG	ASIQ_IDX_T201_C1_HG	U	Main	(NULL)
employee	DBA	dept_id	FP	ASIQ_IDX_T202_C5_FP	N	Main	(NULL)

Example 5

The following variations in syntax both return all indexes in the table department that is owned by table owner DBA:

sp_iqindex department,NULL,DBA

sp_iqindex table_name='department',table_owner='DBA'

table_ name	table_ owner	column_ name	index_ type	index_name	unique_ index	location	remarks
department	DBA	dept_head_id	FP	ASIQ_IDX_T201_C3_FP	N	Main	(NULL)
department	DBA	dept_id	FP	ASIQ_IDX_T201_C1_FP	N	Main	(NULL)
department	DBA	dept_id	HG	ASIQ_IDX_T201_C1_HG	U	Main	(NULL)
department	DBA	dept_name	FP	ASIQ_IDX_T201_C2_FP	N	Main	(NULL)

Example 6

The following variations in syntax for sp_iqindex_alt both return indexes on the table employee that contain the column city. The index emp_loc is a multicolumn index on the columns city and state. sp_iqindex_alt displays one row per column for a multicolumn index.

sp_iqindex_alt employee,city

sp_iqindex_alt table_name='employee',
               column_name='city'

table_ name	table_ owner	column_ name	index_ type	index_name	unique_ index	remarks
employee	DBA	city	FP	ASIQ_IDX_T452_C7_FP	N	(NULL)
employee	DBA	city	HG	emp_loc	N	(NULL)
employee	DBA	state	HG	emp_loc	N	(NULL)

Notice that the output from the sp_iqindex procedure for the same table and column is slightly different:

sp_iqindex employee,city

sp_iqindex table_name='employee',column_name='city'

table_ name	table_ owner	column_ name	index_ type	index_name	unique_ index	location	remarks
employee	DBA	city	FP	ASIQ_IDX_T452_C7_FP	N	Main	(NULL)
employee	DBA	city,state	HG	emp_loc	N	Main	(NULL)