Wednesday, April 13, 2016

Unused indexes -- updated script

DECLARE @cmd VARCHAR(6000)

DECLARE @TempIndexDrop TABLE (
       [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


SET @cmd = 'USE [?] ;
select db_name(database_id) as ''Database_Name'', object_name(i.object_id) as ''Object_Name'',
i.name as ''Index_Name''
, ''USE ['' + db_name(database_id) + '']; drop index [''  + i.name + ''] on ['' + sch.name + ''].['' + obj.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 = 0
and i.name not like ''pk%''
and ''?'' not in (''master'', ''model'',''tempdb'',''msdb'')

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

INSERT INTO @TempIndexDrop 
(
[Database_Name]
,[Object_Name]
,[Index_Name] 
,[Drop_Statement] 
,[user_seeks]
,[user_scans]
,[user_lookups]
,[SpaceSavedMeg]
)
Exec sp_msforeachdb @cmd

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

SELECT *
FROM @TempIndexDrop
ORDER BY [Database_Name], [Object_Name]

3 comments:


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

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. Hi, It's very best advantageous blogs.I read this subjects blog such a great blog and good sharing I'll be like this informative post.Thank you for selecting the time to provide us with your valuable knowledge.C#.NET Online Course

    ReplyDelete