List Unused SQL Server Indexes


The query below will give the list of all unused indexes in the database, including the DROP statement.

SELECT AS ObjectName, AS IndexName, 
       i.index_id AS IndexID, 
       dm_ius.user_seeks AS UserSeek, 
       dm_ius.user_scans AS UserScans, 
       dm_ius.user_lookups AS UserLookups, 
       dm_ius.user_updates AS UserUpdates, 
       'DROP INDEX ' + QUOTENAME( + ' ON ' 
        + QUOTENAME( + '.' 
        AS 'drop 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, 
                    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
ORDER BY (dm_ius.user_seeks 
          + dm_ius.user_scans 
          + dm_ius.user_lookups) ASC

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s