USE THIS: sp_helpindex

Since I’ve rewritten sp_helpindex a few times, I have a few blogs posts in this category. Each time I do an update I’ll make the NEW version titled USE THIS and I’ll change all others to (OLD).

The point of this updated version of sp_helpindex is to add details for newer functionality (included columns and filters) as well as better describe what’s REALLY in your indexes (at the leaf level AND up the b-tree). This particular version won’t error for new index types AND it’s “generic” and not version specific (meaning you don’t have to have an sp_SQLskills_SQL2012_helpindex and instead just have one sp_SQLskills_helpindex across versions). You use sp_SQLskills_helpindex in exactly the same way as sp_helpindex; if you can pass it into sp_helpindex then you can pass it into sp_SQLskills_helpindex!

EXAMPLES:

sp_helpindex tablename WORKS
sp_helpindex owner.tablename DOES NOT WORK
sp_helpindex 'owner.tablename' WORKS

 

To setup this version of sp_helpindex, you need TWO scripts. Both scripts are generic and work on all versions from SQL Server 2005 through SQL Server 2016. To produce the detailed output, you need to first create these system procedures.

 

Step 1: Setup sp_SQLskills_ExposeColsInIndexLevels

Create this procedure first: sp_SQLskills_ExposeColsInIndexLevels.sql  (7 kb).

This is what gives us the tree/leaf definitions.

Step 2: Setup the replacement procedure for sp_helpindex -> sp_SQLskills_helpindex

Create the new sp_helpindex, use: sp_SQLskills_helpindex.sql  (18 kb) to create sp_SQLskills_helpindex.

 

Have fun! And, post a comment and/or shoot me an email if you find any issues!

Thanks for reading,
Kimberly

7 thoughts on “USE THIS: sp_helpindex

    1. Hey there Alex – I didn’t really change all that much but I did update/add this sentence (because of your comment ;-) ) back into the post: This particular version won’t error for new index types AND it’s “generic” and not version specific (meaning you don’t have to have an sp_SQLskills_SQL2012_helpindex and instead just have one sp_SQLskills_helpindex across versions).

      So, it’s mostly about just making it work across all versions and not having problems with some of the new index types…

      Have fun!
      k

  1. Looks like I can compile these on Azure SQL Database ok by commenting-out the “use master” and the sp_MS_marksystemobject call at the bottom. So each database would need to contain these two procedures vs. just one in master for the server.

    Any gotchas you are aware of? Thanks!

Leave a Reply

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

Other articles

SQLskills SQL101: Partitioning

Continuing on our path to understanding the basics and core concepts, there’s a big topic that’s often greatly misunderstood and that’s partitioning. I know I’m

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.