How to get list of all Indexes of a table in MS-SQL


How to get the list of all existing indexes of a table:

There is a system stored procedure “sp_helpindex”, which can be used to get the list of all existing indexes by supplying table name to it. We can use this stored procedure as -

  EXECUTE sp_helpindex my_Table_Name

Let's see it by practical. I am creating a table with 3 indexes on it. I am creating 2 indexes while creating my table tblTestAKumar and later adding 1 index using CREATE INDEX command. By default CREATE INDEX command is going to add a Non-Clustered index to my table.

 

 USE tempdb
DROP TABLE tblTestAKumar
CREATE TABLE tblTestAKumar
(
AutoID int identity primary key nonclustered,
VendorCode varchar(50) unique clustered,
LastUpdatedOn datetime
);

We will add a one more index on LastUpdatedOn column using below command as-

 CREATE INDEX indexLastUpdatedOn_tblTestAkumar ON tblTestAKumar ( LastUpdatedOn )

  

Now we will execute system stored procedure ”sp_helpindex”  by supplying our table as –

 EXECUTE sp_helpindex tblTestAKumar

 

This gives us following result:

spResult.jpg


 Thanks for reading :)

 

Ebook Download
View all
Learn
View all