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]
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]
ReplyDeleteThanks for posting such an interesting inforamtion, Please keep updates us and make us up to date....
This comment has been removed by a blog administrator.
ReplyDeleteHi, 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