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.
HERE IS A LINK TO THE NEW VERSION: https://www.sqlskills.com/blogs/kimberly/use-this-new-sql-server-2012-rewrite-for-sp_helpindex/
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.
7 thoughts on “(OLD): 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.
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.
Wrong link to Michelle blog try this
Sorry about that
Thanks for the updates – are we okay to use this in commercial code – i.e. in scripts/databases we distribute with our software?
Hey there Richard – You can certainly base your own analysis on this code but we don’t provide our code as “supported”. Our disclaimer is that this is only intended as a supplement to demos and lectures given by SQLskills instructors.
THIS CODE AND INFORMATION ARE PROVIDED “AS IS” WITHOUT WARRANTY OF
ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED
TO THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
So, depending on what your application is planning to do – you should do adequate testing/analysis on your own code to be able to support it appropriately.
Hope that helps!