Code to identify unused and lightly used indexes

This code looks in all databases for a server...

(revised in conjunction with Mark Nelson)

DECLARE @cmd VARCHAR(6000)

 

CREATE TABLE tempdb..TempIndexDrop(

       [Database_Name] [nvarchar](128) NULL,

       [Object_Name] [nvarchar](128) NULL,

       [Index_Name] [sysname] NULL,

       [Drop_Statement] [nvarchar](283) NULL,

       [user_seeks] [bigint] NOT NULL,

       [user_scans] [bigint] NOT NULL,

       [user_lookups] [bigint] NOT NULL,

       [SpaceSavedMeg] decimal (12,2) not null

) ON [PRIMARY]

 

SET @cmd = 'USE [?] ;

 

INSERT INTO tempdb..TempIndexDrop select db_name(database_id) as ''Database_Name'', object_name(i.object_id) as ''Object_Name'',

i.name as ''Index_Name'', ''drop index ['' + sch.name + ''].['' + ''obj.name'' + ''].['' + i.name + '']'' as ''Drop_Statement'',

user_seeks, user_scans, user_lookups,

(select sum(s.used_page_count)*8./1024. from sys.dm_db_partition_stats s where i.object_id = s.object_id and i.index_id = s.index_id)

from sys.dm_db_index_usage_stats ius join sys.indexes i

on ius.index_id = i.index_id and ius.object_id = i.object_id join sys.objects obj

on ius.object_id = obj.object_id join            sys.schemas sch

on            obj.schema_id = sch.schema_id

where       database_id = db_id(''?'') and user_seeks + user_scans + user_lookups <100 and="" span="" style="mso-spacerun: yes;">      i.name not like ''PK_%''   and     object_name(i.object_id) NOT LIKE (''sys%'') 

order by       object_name(i.object_id),       i.name'

 

 

--print @cmd

Exec sp_msforeachdb @cmd

 

SELECT SUM(SpaceSavedMeg) as 'Potential Storage Savings' FROM  tempdb..TempIndexDrop

 

SELECT * FROM tempdb..TempIndexDrop

ORDER BY Database_Name, Object_Name

 

DROP TABLE tempdb..TempIndexDrop