How to find Index information - sp_msindexspace,sp_helpindex,sp_mshelpindex

Index are very important part for any database.
We need to regularly monitor index and maintain index for better performance of the system.
I have already written an article on how to find highly used index



Today I am writing on how to get basic information related to index

Lets create a table

create table index_test
(id int,
data1 varchar(100),
data2 varchar(200))

Now we will create a clustered index

create clustered index idx_clust_index_test_id on index_test(id)

Now we will create a non clustered index.SQL 2005 is also supporting included column.
we will create index with included column


create index idx_index_test_data1 on index_test(data1)
include (data2)


Now if we want to get info for indexes for this table.
Here are various option available to get all this different information

sp_helpindex index_test
This will provide basic info for the index

Result

index_name index_description index_keys

idx_clust_index_test_id clustered located on PRIMARY id
idx_index_test_data1 nonclustered located on PRIMARY data1

Below two are undocumented stroe proc.

sp_mshelpindex index_test
This will provide basic info along with index_id, fillfactor and status of fulltextkey , computed , IsTable

Result is large so not published here

sp_msindexspace index_test

Index ID Index Name Size (KB) Comments
1 idx_clust_index_test_id 16 Size excludes actual data.
2 idx_index_test_data1 40 (None)


Now if want to look at system tables for index infomation

select object_name(object_id) object, * from sys.indexes where object_id = object_id ('index_test')



select object_name(c.object_id) object,i.name index_name,i.index_id,cl.name column_name, c.index_column_id,c.key_ordinal,c.partition_ordinal,c.is_descending_key,c.is_included_column from sys.index_columns c inner join sys.indexes i on c.object_id = i.object_id and c.index_id = i.index_id inner join sys.columns cl on c.object_id = cl.object_id and c.column_id = cl.column_id
where c.object_id = object_id ('index_test')




Remember on idx_index_test_data1 we have one included column data2. no system proc will give info for included column but above query will give infor included column also. sys.index_column has one column is_included_column give us info either the column is included.