Transact-SQL Reference

INDEXPROPERTY

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 or indexed view 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.

Property Description
IndexDepth Depth of the index.

Returns the number of levels the index has.

IndexFillFactor Index specifies its own fill factor.

Returns the fill factor used when the index was created or last rebuilt.

IndexID Index ID of the index on a specified table or indexed view.
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

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

IsPadIndex Index specifies space to leave open on each interior node.

1 = True
0 = False
NULL = Invalid input

IsPageLockDisallowed 1 = Page locking is disallowed through sp_indexoption.
0 = Page locking is allowed.
NULL = Invalid input
IsRowLockDisallowed 1 = Row locking is disallowed through sp_indexoption.
0 = Row locking is allowed.
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


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

Control-of-Flow Language

CREATE INDEX

DELETE

INSERT

Meta data Functions

Operators (Logical Operators)

UPDATE

WHERE