INDEXPROPERTY (T-SQL)

Returns the named index property value given a table identification number, index name, and property name.

Syntax

INDEXPROPERTY(table_ID, index, property)

Arguments

table_ID

Is an expression containing the identification number of the table for which to provide index property information. table_ID is int.

index

Is an expression containing the name of the index for which to return property information. index is nvarchar(128).

property

Is an expression containing the name of the database property to return. property is varchar(128), and can be one of these values.

 

Value	Description	Value returned
IndexDepth	Depth of the index.	Number of levels the index has.
IsAutoStatistics	Index was generated by the auto create statistics option of sp_dboption.	1 = True 0 = False NULL = Invalid input
IsClustered	Index is clustered.	1 = True 0 = False NULL = Invalid input
IsStatistics	Index was created by the CREATE STATISTICS statement or by the auto create statistics option of sp_dboption. Statistics indexes are used as a placeholder for column-level statistics.	1 = True 0 = False NULL = Invalid input
IsUnique	Index is unique.	1 = True 0 = False NULL = Invalid input
IndexFillFactor	Index specifies its own fill factor.	Fill factor used when the index was created or last rebuilt.
IsPadIndex	Index specifies space to leave open on each interior node.	1 = True 0 = False NULL = Invalid input
IsFulltextKey	Index is the full-text key for a table.	1 = True 0 = False NULL = Invalid input
IsHypothetical	Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column level statistics.	1 = True 0 = False NULL = Invalid input

Return Types

int

Examples

This example returns the setting for the IsPadIndex property for the UPKCL_auidind index of the authors table.

USE pubs

SELECT INDEXPROPERTY(OBJECT_ID('authors'), 'UPKCL_auidind',

    'IsPadIndex')

See Also