update to usp_helpindexusagestats
Here's an update to the utility SP [usp_helpindexusagestats] I posted a few weeks back, in response to a comment from Mitch Wheat that he was receiving empty result sets, which could have occurred if the original version was run against a system with an empty cache for the db_dm_index_usage_stats() DMV. This version outer joins output from the DMV against output from MS shipped [sp_helpindex] vs the original version's inner join approach, which most likely explains the experience Mitch had the original version.
create
procedure [dbo].[usp_helpindexusagestats]
@tablename
varchar(1000)
as
set
nocount on
--collect output from system supplied [sp_helpindex]
if
object_id('tempdb..#helpindex') > 0 drop table #helpindex
create
table #helpindex (
index_name
varchar (1000) not null primary key
, index_description varchar (1000) null
, index_keys varchar (1000) null)
insert
#helpindex exec sp_helpindex @tablename
alter
table #helpindex add inccols varchar(1000) null
--cursor over output, adding included columns
declare
cr cursor for
select
si.name, sc.name
from
sysobjects so
join
sysindexes si on so.id = si.id
join
sys.index_columns ic on si.id = ic.object_id and si.indid = ic.index_id
join
sys.columns sc on ic.object_id = sc.object_id and ic.column_id = sc.column_id
where
so.xtype = 'U'
and so.name = @tablename
and ic.is_included_column = 1
order
by si.name, ic.index_column_id
declare
@siname varchar(1000), @scname varchar(1000)
open
cr
fetch
next from cr into @siname, @scname
while
@@fetch_status = 0
begin
update #helpindex set inccols = isnull(inccols , '') + @scname + ', ' where index_name = @siname
fetch next from cr into @siname, @scname
end
update
#helpindex set inccols = left(inccols, datalength(inccols) - 2)
where
right(inccols, 2) = ', '
close
cr
deallocate
cr
--return output, joining index usage stats DMV output
select
hi.index_name, hi.index_description, hi.index_keys
,
hi.inccols as included_columns, ius.index_id
,
user_seeks, user_scans, user_lookups, user_updates
,
last_user_seek, last_user_scan, last_user_lookup
from
#helpindex hi
join
sysindexes si on si.name = hi.index_name collate database_default
left
join sysobjects so on si.id = so.id
left
join sys.dm_db_index_usage_stats ius on ius.object_id = si.id
and
ius.index_id = si.indid and ius.database_id = db_id()
drop table #helpindex