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.