SELECT TOP 1000
O.name as table name
, I.name as index name
, i.index_id as index ID
, dm_ius.user_seeks as Search times
, Dm_ius.user_scans as Scan count
, dm_ius.user_lookups as lookup times
, dm_ius.user_updates as update number
, p.tablerows as table row count
, ' DROP INDEX ' + QUOTENAME (i.name)
+ ' on ' + QUOTENAME (s.name) + '. ' + QUOTENAME (object_name (Dm_ius. object_id)) as ' DELETE statement '
From Sys.dm_db_index_usage_stats Dm_ius
INNER JOIN sys.indexes i on i.index_id = dm_ius.index_id and Dm_ius. object_id = i.object_id
INNER JOIN sys.objects o on Dm_ius. object_id = o.object_id
INNER JOIN sys.schemas s on o.schema_id = s.schema_id
INNER JOIN (SELECT SUM (p.rows) tablerows, p.index_id, p.object_id
From Sys.partitions p GROUP by p.index_id, p.object_id) p
On p.index_id = dm_ius.index_id and Dm_ius. object_id = p.object_id
WHERE objectproperty (Dm_ius. object_id, ' isusertable ') = 1
and dm_ius.database_id = db_id ()
and I.type_desc = ' nonclustered '
and I.is_primary_key = 0
and I.is_unique_constraint = 0
and O.name= ' SMT_FCT '--based on the actual modification of the table name
ORDER by (Dm_ius.user_seeks + Dm_ius.user_scans + dm_ius.user_lookups) ASC
SQL Server Tuning----index not in use