Without indexes your SQL Server performance would be nill, your CPU Time would be chewed up with scanning and your disks would be saturated with read requests, but with too many indexes you can/will experience Index Overload Disorder.

These two views work in tandem with one-another, the first view vIndexesWithColumns returns each of the indexes in your database with the names of the first 25 columns it covers. The second view (vDuplicateIndexes) returns the names of the indexes that cover the same columns. This does not indicate that the indexes are 100% duplicated, as the stat may have more than 25 columns, have different fill factors or use Index Include Columns, so do a bit of research on your indexes before you remove anything.

See also: Finding Duplicate SQL Statistics

CREATE VIEW vIndexesWithColumns AS
SELECT
	tbl.[name] AS TableName,
	idx.[name] AS IndexName,
	INDEX_COL( tbl.[name], idx.indid, 1 ) AS col1,
	INDEX_COL( tbl.[name], idx.indid, 2 ) AS col2,
	INDEX_COL( tbl.[name], idx.indid, 3 ) AS col3,
	INDEX_COL( tbl.[name], idx.indid, 4 ) AS col4,
	INDEX_COL( tbl.[name], idx.indid, 5 ) AS col5,
	INDEX_COL( tbl.[name], idx.indid, 6 ) AS col6,
	INDEX_COL( tbl.[name], idx.indid, 7 ) AS col7,
	INDEX_COL( tbl.[name], idx.indid, 8 ) AS col8,
	INDEX_COL( tbl.[name], idx.indid, 9 ) AS col9,
	INDEX_COL( tbl.[name], idx.indid, 10 ) AS col10,
	INDEX_COL( tbl.[name], idx.indid, 11 ) AS col11,
	INDEX_COL( tbl.[name], idx.indid, 12 ) AS col12,
	INDEX_COL( tbl.[name], idx.indid, 13 ) AS col13,
	INDEX_COL( tbl.[name], idx.indid, 14 ) AS col14,
	INDEX_COL( tbl.[name], idx.indid, 15 ) AS col15,
	INDEX_COL( tbl.[name], idx.indid, 16 ) AS col16,
	INDEX_COL( tbl.[name], idx.indid, 17 ) AS col17,
	INDEX_COL( tbl.[name], idx.indid, 18 ) AS col18,
	INDEX_COL( tbl.[name], idx.indid, 19 ) AS col19,
	INDEX_COL( tbl.[name], idx.indid, 20 ) AS col20,
	INDEX_COL( tbl.[name], idx.indid, 21 ) AS col21,
	INDEX_COL( tbl.[name], idx.indid, 22 ) AS col22,
	INDEX_COL( tbl.[name], idx.indid, 23 ) AS col23,
	INDEX_COL( tbl.[name], idx.indid, 24 ) AS col24,
	INDEX_COL( tbl.[name], idx.indid, 25 ) AS col25,
	dpages,
	used,
	rowcnt
FROM
	sysindexes idx
INNER JOIN sys.objects tbl
	ON idx.[id] = tbl.object_id
WHERE
	tbl.is_ms_shipped = 0
	AND indid > 0 
	AND INDEXPROPERTY(tbl.object_id, idx.[name], 'IsStatistics') = 0
GO

CREATE VIEW vDuplicateIndexes AS
SELECT
	l1.TableName, 
	l1.IndexName, 
	l2.IndexName AS DuplicateIndex, 
	l1.col1, 
	l1.col2, 
	l1.col3, 
	l1.col4, 
	l1.col5, 
	l1.dpages,
	l1.used,
	l1.rowcnt
FROM
	vIndexesWithColumns l1 
INNER JOIN vIndexesWithColumns l2
	ON l1.TableName = l2.TableName
	AND l1.IndexName <> l2.IndexName
	AND l1.col1 = l2.col1
	AND COALESCE(l1.col2,'') = COALESCE(l2.col2,'')
	AND COALESCE(l1.col3,'') = COALESCE(l2.col3,'')
	AND COALESCE(l1.col4,'') = COALESCE(l2.col4,'')
	AND COALESCE(l1.col5,'') = COALESCE(l2.col5,'')
	AND COALESCE(l1.col6,'') = COALESCE(l2.col6,'')
	AND COALESCE(l1.col7,'') = COALESCE(l2.col7,'')
	AND COALESCE(l1.col8,'') = COALESCE(l2.col8,'')
	AND COALESCE(l1.col9,'') = COALESCE(l2.col9,'')
	AND COALESCE(l1.col10,'') = COALESCE(l2.col10,'')
	AND COALESCE(l1.col11,'') = COALESCE(l2.col11,'')
	AND COALESCE(l1.col12,'') = COALESCE(l2.col12,'')
	AND COALESCE(l1.col13,'') = COALESCE(l2.col13,'')
	AND COALESCE(l1.col14,'') = COALESCE(l2.col14,'')
	AND COALESCE(l1.col15,'') = COALESCE(l2.col15,'')
	AND COALESCE(l1.col16,'') = COALESCE(l2.col16,'')
	AND COALESCE(l1.col17,'') = COALESCE(l2.col17,'')
	AND COALESCE(l1.col18,'') = COALESCE(l2.col18,'')
	AND COALESCE(l1.col19,'') = COALESCE(l2.col19,'')
	AND COALESCE(l1.col20,'') = COALESCE(l2.col20,'')
	AND COALESCE(l1.col21,'') = COALESCE(l2.col21,'')
	AND COALESCE(l1.col22,'') = COALESCE(l2.col22,'')
	AND COALESCE(l1.col23,'') = COALESCE(l2.col23,'')
	AND COALESCE(l1.col24,'') = COALESCE(l2.col24,'')
	AND COALESCE(l1.col25,'') = COALESCE(l2.col25,'')