Wednesday, April 13, 2016

Unused indexes -- updated script

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]  NULL,
       [user_scans] [bigint]  NULL,
       [user_lookups] [bigint]  NULL,
       [SpaceSavedMeg] decimal (12,2)  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="" i.name="" i.object_id="" like="" nbsp="" not="" object_name="" span="" 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

1 comment:


  1. Thanks for posting such an interesting inforamtion, Please keep updates us and make us up to date....

    ReplyDelete