Identifying the Indexes on a Table

Use sp_helpindex to see the indexes that exist on a table.

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)