sp_iqindex and sp_iqindex_alt procedures

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' ]

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

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 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.

**Table 7-33: 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 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.

Examples

Example 8

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

call sp_iqindex (NULL,'DepartmentID')

sp_iqindex column_name='DepartmentID'

table_ name	table_ owner	column_ name	index_ type	index_name	unique_ index	location	dbspace_id	remarks
Departments	GROUPO	DepartmentID	FP	ASIQ_IDX_T201_C1_FP	N	Main	16387	(NULL)
Departments	GROUPO	DepartmentID	HG	ASIQ_IDX_T201_C1_HG	U	Main	16387	(NULL)
Employees	GROUPO	DepartmentID	FP	ASIQ_IDX_T202_C5_FP	N	Main	16387	(NULL)

Example 9

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

sp_iqindex Departments,NULL,GROUPO

sp_iqindex table_name='Departments',table_owner='DBA'

table_ name	table_ owner	column_ name	index_ type	index_name	unique_ index	location	dbspace_id	remarks
Departments	GROUPO	DepartmentHeadID	FP	ASIQ_IDX_T201_C3_FP	N	Main	16387	(NULL)
Departments	GROUPO	DepartmentID	FP	ASIQ_IDX_T201_C1_FP	N	Main	16387	(NULL)
Departments	GROUPO	DepartmentID	HG	ASIQ_IDX_T201_C1_HG	U	Main	16387	(NULL)
Departments	GROUPO	DepartmentName	FP	ASIQ_IDX_T201_C2_FP	N	Main	16387	(NULL)

Example 10

The following variations in syntax for sp_iqindex_alt both return indexes on the table Employees 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 Employees,City

sp_iqindex_alt table_name='Employees',
               column_name='City'

table_ name	table_ owner	column_ name	index_ type	index_name	unique_ index	dbspace_id	remarks
Employees	GROUPO	City	FP	ASIQ_IDX_T452_C7_FP	N	16387	(NULL)
Employees	GROUPO	City	HG	emp_loc	N	16387	(NULL)
Employees	GROUPO	State	HG	emp_loc	N	16387	(NULL)

The output from sp_iqindex for the same table and column is slightly different:

sp_iqindex Employees,City

sp_iqindex table_name='Employee',column_name='City'

table_ name	table_ owner	column_ name	index_ type	index_name	unique_ index	dbspace_id	location	remarks
Employees	GROUPO	City	FP	ASIQ_IDX_T452_C7_FP	N	16387	Main	(NULL)
Employees	GROUPO	City,State	HG	emp_loc	N	16387	Main	(NULL)