Syntax of INDEXPROPERTY Function :
INDEXPROPERTY ( table_id, index, property )
table_id is an identification number of table or indexed view. It is of type integer.
index is a name of index. It is of type nvarchar(128).
property is a name of database property. It is of type varchar(128) and can be of below 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 ofsp_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
theauto 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 type of INDEXPROPERTY function is an integer.
Examples of INDEXPROPERTY Function :
Example 1 : Use of INDEXPROPERTY function in select clause
SELECT INDEXPROPERTY( OBJECT_ID('Customers'), 'PK_Customers', 'IndexDepth')
Output
2
Above example returns 2 means index is 2 level depth.