sp_iqindexmetadata Procedure

Displays index metadata for a given index.

You can optionally restrict the output to only those indexes on a specified table, and to only those indexes belonging to a specified owner.

Syntax

dbo.sp_iqindexmetadata {'index-name'
[ , 'table-name' [ , 'owner-name' ] ] }

Permissions

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

Usage

Specifying a table name limits output to those indexes belonging to that table. Specifying an owner name limits output to indexes owned by that owner. Omitted parameters default to NULL. You can specify only one index per procedure.

Description

The first row of output is the owner name, table name, and index name for the index.

Subsequent rows of output are specific to the type of index specified.

sp_iqindexmetadata output rows
Index type	Metadata returned
CMP, DATE, DTTM, TIME	Type, Version
FP	Type, Version, LookupPages, Style, LookupEntries, 1stLookupPage, LargeLOBs, SmallLOBs, IQ Unique, LOB Compression (only if column datatype is `LONG VARCHAR` or `LONG BINARY`)
HG	Type, Version, Distinct Keys
HNG	Type, Version, BitsPerBlockmap, NumberOfBits
LD	Type, Version<ld>, Version, Distinct Keys
LF	Type, Version, IndexStatus, NumberOfBlockmaps, BitsPerBlockmap, Distinct Keys
WD	Type, Version, KeySize, Delimiters, DelimiterCount, MaxKeyWordLength, PermitEmptyWord

Examples

The following command displays index information about the HG index hg_index_col54:

sp_iqindexmetadata 'hg_index_col54' , 'metal' , 'DBA';

'DBA',	'metal'	'hg_index_col54'
'Type',	'HG',	''
'Version',	'2',	''
'Distinct Keys',	'0',	''