Unused Index

Here is a useful script to get the unused index in a Database. This may be inaccurate sometime. So you had to apply commonsense before running DROP index query:

SELECT DB_Name() AS [Database], S.Name AS [Schema], O.Name AS [Object], C.Name AS [Column], I.Name AS [Index],
        (CASE WHEN I.IS_Disabled = 1 THEN 'Yes' ELSE 'No' END) AS [Disabled],
        (CASE WHEN I.IS_Hypothetical = 1 THEN 'Yes' ELSE 'No' END) AS Hypothetical,
        Rows = (SELECT SUM(P.Rows) FROM sys.Partitions P WHERE P.INDEX_ID = I.INDEX_ID 
                        AND P.OBJECT_ID = I.OBJECT_ID GROUP BY P.INDEX_ID, P.OBJECT_ID),
        N'USE ' + DB_Name() + N'; DROP INDEX ' + QuoteName(I.Name) + ' ON ' + QuoteName(S.Name) + 
                    '.' + QuoteName(OBJECT_Name(I.OBJECT_ID)) AS 'Drop Statement'
FROM [sys].[Objects] O 
        INNER JOIN [sys].[Indexes] I ON O.[OBJECT_ID] = I.[OBJECT_ID] AND O.[Type] = 'U' AND O.IS_MS_Shipped = 0 AND O.Name <> 'sysDiagrams'
        INNER JOIN [sys].[Tables] T ON T.[OBJECT_ID] = I.[OBJECT_ID]
        INNER JOIN [sys].[Schemas] S ON S.[Schema_ID] = T.[Schema_ID]
        INNER JOIN [sys].[Index_Columns] IC ON IC.[OBJECT_ID] = I.[OBJECT_ID] AND IC.INDEX_ID = I.INDEX_ID
        INNER JOIN [sys].[Columns] C ON C.[OBJECT_ID] = IC.[OBJECT_ID] AND C.COLUMN_ID = IC.COLUMN_ID
WHERE I.[Type] > 0 AND I.IS_Unique_Constraint = 0 AND I.IS_Primary_Key = 0 
        AND NOT EXISTS (SELECT * FROM [sys].[Foreign_Key_Columns] FKC INNER JOIN [sys].[Index_Columns] XIC ON FKC.Parent_COLUMN_ID = XIC.COLUMN_ID AND FKC.Parent_OBJECT_ID = XIC.[OBJECT_ID] WHERE XIC.[OBJECT_ID] = I.[OBJECT_ID] AND XIC.INDEX_ID = I.INDEX_ID)
        AND NOT EXISTS (SELECT * FROM [master].[sys].[DM_db_Index_Usage_Stats] IUS WHERE IUS.Database_ID = DB_ID(DB_Name()) AND IUS.[OBJECT_ID] = I.[OBJECT_ID] AND IUS.INDEX_ID = I.INDEX_ID)