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
