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 |
| IsClustered | Index is clustered.
1 = True |
| IsFulltextKey | Index is the full-text key for a table.
1 = True |
| IsHypothetical | Index is hypothetical and cannot be used directly as a data access path. Hypothetical indexes hold column level statistics.
1 = True |
| IsPadIndex | Index specifies space to leave open on each interior node.
1 = True |
| 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 |
| IsUnique | Index is unique.
1 = True |
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
Operators (Logical Operators)