sp_help and sp_helpindex

When you execute sp_help and sp_helpindex in a database, and give a table’s name, you see information about which segments store the table or its indexes.

For example:

sp_helpindex authors
index_name  index_keys  index_description  index_max_rows_per_page  
     index_fillfactor  index_reservepagegap  index_created  
     index_local
----------  ----------  -----------------  -----------------------
     ----------------  --------------------  -------------
     -----------
auidind     au_id       clustered,unique                         0
                    0                    0   Apr26 2005  4:04PM
     Global Index
aunwind    au_lname,au_fname  nonclustered,unique               0
                    0                    0   Apr26 2005  4:04PM
     Global Index
(2 rows affected)
index_ptn_name        index_ptn_seg
--------------------  --------------
auidind_400001425     default
aunmind_400001425     default