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_name index_description index_keys ClstIndex_Name_FF10 clustered located on PRIMARY Name Index_Name_NonClstd nonclustered located on PRIMARY Name Index_Name_Unique nonclustered, unique located on PRIMARY Name
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
