I realize that I've created quite a few posts (and rewrites) around sp_helpindex and recently I've been recommending that most folks use my latest version (jokingly, this is sp_helpindex9) but it's not so obvious where to get it and/or what to do to install it. To make it easier… I'm just going to do a post like this EVERY TIME I have a new version and so there won't be anything else to review/read.
All versions use a base procedure that builds the columns needed to produce the detailed output. So, you need to setup TWO procedures.
Step 1: setup sp_SQLskills_ExposeColsInIndexLevels
Create this procedure first: sp_SQLskills_ExposeColsInIndexLevels.sql (6.73 kb).
This is what gives us the tree/leaf definitions. And, this works for both SQL Server 2005 and SQL Server 2008.
Step 2: setup the replacement procedure for sp_helpindex. This IS version specific:
On SQL Server 2005, use: sp_SQLskills_SQL2005_helpindex.sql (10.50 kb) to create sp_SQLskills_SQL2005_helpindex.
On SQL Server 2008, use: sp_SQLskills_SQL2008_helpindex (sp_helpindex9).sql (11.06 kb) to create sp_SQLskills_SQL2008_helpindex.
Step 3: Optionally, setup this procedure to be invoked through a keyboard shortcut using Tools, Options, Environment/Keyboard. I usually make it Ctrl+F1 and I described how to do this here.
Enjoy!
Kimberly
4 Responses to USE THIS: sp_helpindex rewrites
Cool updates and thank you! One note on this, if you change the reference from sys.tables to sys.objects in the section that checks for include columns (@inc_Count) and the section that inserts into the #IncludedColumns you will be able to see included columns on indexed views as well as the tables. I haven’t found any adverse affect by making this change yet either.
Hope this helps.
Thanks David – I’ll play around with that.
Cheers!
kt
Great Stuff Kimberley(KT) and everybody else who helps
Here’s my bit…
A small tip for contact developers who don’t want to install KT’s sp_..(or who are’t allowed to, ie not dba)
Use the sql only from KTs sp_helpindex2 to see current indexes(sic) and use as a query.
Use Michelle Ufford‘s query to find missing indexes and supplant the tables, one at a time, from that query into KTs query.
take care, and test with SQL Profiler and also with related update/insert actions, hence one table at a time.
Measure twice, cut once.
regards
GregJF
Oops
Wrong link to Michelle blog try this
Sorry about that
GregJF