It is useful to be able to see all indexes on a table. There is the shipped stored procedure sp_helpindex but the following query can reveal some more inforamtion such as the Included and Filter columns.
Declare @table varchar(255) = 'MyTable', @schema varchar(50) ='dbo';
with IDX as
(
SELECT schema_name(o.schema_id) as [SCHEMA],O.NAME AS [TABLE],AC.NAME AS [COLUMN],
I.name AS [INDEX],I.type_desc AS [TYPE], IC.is_included_column AS [INCLUDED],
isnull(I.filter_definition,'N/A') AS [FILTER],I.is_disabled as [DISABLED],allow_page_locks as [PAGE LOCKS], allow_row_locks as [ROW LOCKS]
FROM SYS.all_columns AC JOIN SYS.objects O ON AC.object_id=O.object_id
JOIN SYS.index_columns IC ON IC.object_id=O.object_id AND AC.column_id=IC.column_id
JOIN SYS.indexes I ON I.index_id=IC.index_id AND I.object_id=O.object_id
WHERE O.NAME =@table and schema_name(o.schema_id)=@schema
)
select [SCHEMA],[TABLE],[INDEX],[TYPE],
stuff((select ','+[COLUMN] from IDX ii
where ii.[TABLE]=i.[TABLE] and ii.[INDEX]=i.[INDEX] and ii.INCLUDED=0
for xml path ('')),1,1,'') as [COLUMNS],
isnull(stuff((select ','+[COLUMN] from IDX ii where ii.[TABLE]=i.[TABLE] and ii.[INDEX]=i.[INDEX] and ii.INCLUDED=1 for xml path ('')),1,1,''),'N/A') as [INCLUDED],[FILTER],[DISABLED] ,[ROW LOCKS],[PAGE LOCKS]
from idx i
group by [SCHEMA],[TABLE],[INDEX],[TYPE],[FILTER],[DISABLED],[ROW LOCKS],[PAGE LOCKS]