Wednesday, April 13, 2016

Unused indexes -- updated script


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'', as ''Index_Name''
, ''USE ['' + db_name(database_id) + '']; drop index [''  + + ''] on ['' + + ''].['' + + '']'' 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
database_id = db_id(''?'') and
user_seeks + user_scans + user_lookups = 0
and not like ''pk%''
and ''?'' not in (''master'', ''model'',''tempdb'',''msdb'')

order by       object_name(i.object_id),'

INSERT INTO @TempIndexDrop 
Exec sp_msforeachdb @cmd

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

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


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

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

  3. Great post. Needed to write simple word that Thanks for suggestions. Keep it up! best sap simple finance online training institute in hyderabad

  4. 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

  5. Really you have done great job,There are may person searching about that topic. now they will easly find your post
    SQL Azure Online Training
    Azure SQL Training
    SQL Azure Training