IMPORTANT UPDATE:
PLEASE REVIEW THIS POST FOR THE UPDATED SCRIPTS: USE THESE: Updates to SQLskills Index Procedures
****************************************************
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 – rename download to .sql).
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 – rename download to .sql) 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 “(OLD): sp_helpindex”
Cool! What changed?
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
That’s a welcome change, thanks a lot.
Have you made any changes recently to your Find Duplicate Indexes scripts?
No, those still work well on any release. But, I guess I could change the name. Feel free to do so!
Cheers,
k
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!
None that I’ve heard. It should work as a local proc!
Enjoy!
k