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.
To use my version of sp_helpindex, you need TWO scripts. One script is version-specific and the other works on versions 2005, 2008/R2 and 2012. All versions need this generic base procedure to produce the detailed output.
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. And, this works for all versions: 2005, 2008/R2, and 2012.
Step 2: Setup the replacement procedure for sp_helpindex. This IS version specific:
On SQL Server 2005, use: sp_SQLskills_SQL2005_helpindex.sql (11 kb) to create sp_SQLskills_SQL2005_helpindex.
On SQL Server 2008, use: sp_SQLskills_SQL2008_helpindex.sql (12 kb) to create sp_SQLskills_SQL2008_helpindex. (NOTE: This does run on SQL Server 2012 but if your table has a columnstore index, it will generate an error.)
On SQL Server 2012, use: sp_SQLskills_SQL2012_helpindex.sql (12 kb) to create sp_SQLskills_SQL2012_helpindex.
Step 3: Setup a hot-key combination
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.
On SQL Server 2012, the output will look like the following (index_id 5 is a columnstore index):
|1||0||[member_ident]||clustered, unique, primary key located on PRIMARY||[member_no]||NULL||NULL||[member_no]||All columns “included” – the leaf level IS the data row.|
|2||0||[member_corporation_link]||nonclustered located on PRIMARY||[corp_no]||NULL||NULL||[corp_no], [member_no]||[corp_no], [member_no]|
|3||0||[member_region_link]||nonclustered located on PRIMARY||[region_no]||NULL||NULL||[region_no], [member_no]||[region_no], [member_no]|
|4||0||[LastNameInd]||nonclustered located on PRIMARY||[lastname]||NULL||NULL||[lastname], [member_no]||[lastname], [member_no]|
|5||0||[columnstore_index]||nonclustered columnstore located on PRIMARY||n/a, see columns_in_leaf for details||n/a, columnstore index||n/a, columnstore index||n/a, columnstore index||Columns with column-based index: [member_no], [lastname], [firstname]|
I hope this helps you when looking at your indexes!