T-SQL » Indexes » Viewing an Index

Syntax:
sp_helpindex table1

INDEXPROPERTY ( OBJECT_ID ( table1 ), index1, property1 )
table1
The name of a table in the current database.
OBJECT_ID (table1)
Returns the identification number of the table table1.
index1
An expression containing the name of the index.
property1
An expression containing the database property which is to be retrieved.

SQL Server allows us to view all the indexes in a table, the space used by an index, and also the properties of an index.

The sp_helpindex command can be used to view all of the indexes in a table. This will display such information as their description and their index keys.

Using the INDEXPROPERTY command will display the properties of an index. The named index property value for a given identification number, index and property can be shown.

Examples

Code:
sp_helpindex Students;
Output:
index_nameindex_descriptionindex_keys
ClstIndex_Name_FF10clustered located on PRIMARYName
Index_Name_NonClstdnonclustered located on PRIMARYName
Index_Name_Uniquenonclustered, unique located on PRIMARYName




Explanation:

In the above example, sp_helpindex displays the name of indexes, their description and their index keys for the specified table.

Language(s): MS SQL Server
Code:
SELECT INDEXPROPERTY (OBJECT_ID ('Students'), 'ClstdIndex_Name_FF10','IndexFillFactor');
Output:
10
(1 row(s) affected)
Explanation:

The above example displays the fillfactor property of the ClstdIndex_Name_FF10 index from the 'Students' table.

Language(s): MS SQL Server