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_description index_keys
index_max_rows_per_page
------------- ---------------------------- -------------
-----------------------
nmind clustered located on default pname, sname
0
postalcodeind nonclustered located on default postalcode
0
(2 rows affected, return status = 0)
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, 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)
