Identifying Indexes on a Table

When I'm looking over a troublesome query for tuning options I find it important to have the full picture of what other indexes already exist on the tables in my query.  I'd want this in order to determine one or more of the following situations before proceeding with the possibility of adding (yet) another index: 

All of these questions can be addressed by querying three system catalog views: sys.indexes, sys.index_columns and sys.all_columns: 

--=============================================--What indexes are on this table?--=============================================SELECT OBJECT_NAME(I.[object_id]) AS [object_name]    , I.name AS index_name    , IC.index_column_id    , AC.name AS column_name    , IC.[is_descending_key]    , IC.is_included_column    , I.type_desc AS index_type    , I.is_primary_key    , I.is_unique    , I.is_unique_constraintFROM sys.indexes I    INNER JOIN sys.index_columns IC        ON I.index_id = IC.index_id        AND I.[object_id] = IC.[object_id]    INNER JOIN sys.all_columns AC        ON IC.[object_id] = AC.[object_id]        AND IC.column_id = AC.column_idWHERE I.object_id = OBJECT_ID('')ORDER BY I.is_primary_key DESC , I.name , IC.is_included_column , IC.index_column_id;--=============================================-- By Tim Ford, SQL Cruise (www.sqlcruise.com)--=============================================

You'll notice that I employed a template parameter for the table name.  Simply use the keyboard shortcut of Cntl+Shift+M to replace that value with the appropriate table name.  Further information on template parameters and the Template Explorer can be found here.

The query results provide us with the following information to make educated choices in how to proceed:

All things valuable in making decisions about how to proceed in performance tuning when indexing changes are one of the options on the (ahem) "table".