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:
Thanks for reading :)
0 comments :
Post a Comment