(OLD): sp_helpindex rewrites

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.

Enjoy!
Kimberly

7 thoughts on “(OLD): sp_helpindex rewrites”

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

    Reply

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

    Reply

  5. Hi Kimberly,

    Thanks for the updates – are we okay to use this in commercial code – i.e. in scripts/databases we distribute with our software?

    Thanks,

    Richard

    Reply

    1. 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
      PARTICULAR PURPOSE.

      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!
      Kimberly

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course