Script to list all indexes and their columns to find the duplicate ones

SELECT




TableName
  , IndexName

, IndexType

, [1] AS Column1

, [2] AS Column2

, [3] AS Column3

, [4] AS Column4

, [5] AS Column5

, [6] AS Column6

, [7] AS Column7

, [8] AS Column8

FROM




(
  SELECT

OBJECT_NAME (ic.object_id) AS TableName

, COL_NAME(si.object_id, column_id) AS ColumnName

, si.[name] AS IndexName

, ic.key_ordinal

, si.[type_desc] AS IndexType

FROM sys.index_columns ic

INNER JOIN sys.indexes si

ON ic.index_id = si.index_id

AND ic.object_id = si.object_id

INNER JOIN sys.objects so

ON ic.object_id = so.object_id

WHERE so.is_ms_shipped <> 1

) PivotData




PIVOT

(
  

MIN (ColumnName)

FOR [key_ordinal] IN ([1], [2], [3], [4], [5], [6], [7], [8])




)
  AS ColumnPivot

ORDER BY [TableName], [IndexName], [Column1], [Column2], [Column3], [Column4], [Column5], [Column6], [Column7], [Column8]


Ref: I refereed an online source and the script is very similar to the source but with some details removed to make it suit my needs