sp_iqindex and sp_iqindex_alt procedures

Function

Lists information about indexes.

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.

Description

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-31: 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)

See also

“FP_LOOKUP_SIZE option,” “INDEX_ADVISOR option,” and “MINIMIZE_STORAGE option” in Chapter 2, “Database Options” in Reference: Statements and Options

Chapter 6, “Using Sybase IQ Indexes” in System Administration Guide: Volume 1.