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
