Inspired by a Stack Overflow post:
select
t.name as [Table Name], col.name as [Column Name], ind.name as [Index Name]
from
sys.indexes ind
inner join
sys.index_columns ic on
ind.object_id = ic.object_id and ind.index_id = ic.index_id
inner join
sys.columns col on
ic.object_id = col.object_id and ic.column_id = col.column_id
inner join
sys.tables t on
ind.object_id = t.object_id
where
ind.is_primary_key = 0
and ind.is_unique = 0
and ind.is_unique_constraint = 0
and t.is_ms_shipped = 0
order by
t.name, ind.name, ind.index_id, ic.index_column_id
