To see the indexes that exist on a table, you can use sp_helpindex. Here is a report on the friends_etc table:
sp_helpindex friends_etc
index_name index_keys index_description index_max_rows_per_page ------------- ---------- ----------------- -------------------------- nmind pname,sname clustered 0 postalcodeind postalcode nonclustered 0 index_fillfactor index_reservepagegap index_created index_local ----------------- -------------------- --------------- ------------ 0 0 May 24 2005 1:49PM Global Index 0 0 May 24 2005 1:49PM Global Index (2 rows affected) index_ptn_name index_ptn_seg ------------------ --------------- nmind_1152004104 default postalcodeind_1152004104 default (2 rows affected)
sp_help runs sp_helpindex at the end of its report.
sp_statistics returns a list of indexes on a table. For example:
sp_statistics friends_etc
table_qualifier table_owner table_name non_unique index_qualifier index_name type seq_in_index column_name collation cardinality pages -------------------------------- -------------------------------- -------------------------------- ---------- -------------------------------- ------------------------------- ------ ------------ -------------------------------- --------- ----------- ----------- pubs2 dbo friends_etc NULL NULL NULL 0 NULL NULL NULL 0 1 pubs2 dbo friends_etc 1 friends_etc nmind 1 1 pname A 0 1 pubs2 dbo friends_etc 1 friends_etc nmind 1 2 sname A 0 1 pubs2 dbo friends_etc 1 friends_etc postalcodeind 3 1 postalcode A NULL NULL (4 rows affected) table_qualifier table_owner table_name index_qualifier index_name non_unique_type seq_in_index column_name collation index_id cardinality pages status status2 ---------------- --------- ---------- --------------- ----------- ---------------- ------------ ----------- --------- --------- ----------- ----- ------ -------- pubs2 dbo friends_etc friends_etc nmind 1 1 1 pname A 0 1 16 0 pubs2 dbo friends_etc friends_etc nmind 1 1 2 sname A 0 1 16 0 pubs2 dbo friends_etc friends_etc postalcodeind 1 3 1 postalcode A NULL NULL 0 0 pubs2 dbo friends_etc NULL NULL NULL 0 NULL NULL NULL 0 0 1 0 0 (4 rows affected) (return status = 0)
In addition, if you follow the table name with “1”, sp_spaceused reports the amount of space used by a table and its indexes. For example:
sp_spaceused friends_etc, 1
index_name size reserved unused -------------------- ---------- ---------- ---------- nmind 2 KB 32 KB 28 KB postalcodeind 2 KB 16 KB 14 KB name rowtotal reserved data index_size unused ------------ ---------- ------------ ------- --------------- -------- friends_etc 1 48 KB 2 KB 4 KB 42 KB (return status = 0)
