http://beyondrelational.com/modules/30/scripts/485/scripts/11430/tsql-script-to-identify-unused-indexes.aspx?utm_source=brnewsletter&utm_medium=email&utm_campaign=2012Mar
WITH cte AS (02.SELECT
03.'['+ c.name + '].[' + o.name + ']' AS TableName,
04.i.name AS IndexName,
05.i.index_id AS IndexID,
06.user_seeks + user_scans + user_lookups AS Reads,
07.user_updates AS Writes,
08.(
09.SELECT SUM(p.rows)
10.FROM sys.partitions p
11.WHERE p.index_id = s.index_id
12.AND s.object_id = p.object_id
13.) AS TotalRows,
14.CASE
15.WHEN s.user_updates < 1 THEN 100
16.ELSE 1.00 * (s.user_seeks + s.user_scans + s.user_lookups)
17./ s.user_updates
18.END AS ReadsPerWrite,
19.'DROP INDEX ' + QUOTENAME(i.name)
20.+ ' ON ' + QUOTENAME(c.name)
21.+ '.' + QUOTENAME(OBJECT_NAME(s.object_id))
22.AS 'DropSQL'
23.FROM sys.dm_db_index_usage_stats s
24.INNER JOIN sys.indexes i ON i.index_id = s.index_id
25.AND s.object_id = i.object_id
26.INNER JOIN sys.objects o on s.object_id = o.object_id
27.INNER JOIN sys.schemas c on o.schema_id = c.schema_id
28.WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
29.AND s.database_id = DB_ID()
30.AND i.type_desc = 'nonclustered'
31.AND i.is_primary_key = 0
32.AND i.is_unique_constraint = 0
33.AND
34.(
35.SELECT SUM(p.rows)
36.FROM sys.partitions p
37.WHERE p.index_id = s.index_id
38.AND s.object_id = p.object_id
39.) > 10000
40.)
41.SELECT * FROM cte
42./*
43.TableName Name ID Reads Writes Rows ReadsPerWrite DropSQL
44.
45.[dbo].[t1] IDX_1 20 1227972 996081 78599 1.23280335635 DROP ..
46.[dbo].[t2] IDX_2 21 51359 33912 78599 1.51447865062 DROP ..
47.[dbo].[t3] IDX_3 22 235280 159808 78599 1.47226672006 DROP ..
48.*/
