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.

The Output

On SQL Server 2012, the output will look like the following (index_id 5 is a columnstore index):

index_idis_disabledindex_nameindex_descriptionindex_keysincluded_columnsfilter_definitioncolumns_in_treecolumns_in_leaf
10[member_ident]clustered, unique,   primary key located on PRIMARY[member_no]NULLNULL[member_no]All columns   “included” – the leaf level IS the data row.
20[member_corporation_link]nonclustered located   on PRIMARY[corp_no]NULLNULL[corp_no],   [member_no][corp_no],   [member_no]
30[member_region_link]nonclustered located   on PRIMARY[region_no]NULLNULL[region_no],   [member_no][region_no],   [member_no]
40[LastNameInd]nonclustered located   on PRIMARY[lastname]NULLNULL[lastname],   [member_no][lastname],   [member_no]
50[columnstore_index]nonclustered   columnstore located on PRIMARYn/a, see   columns_in_leaf for detailsn/a, columnstore   indexn/a, columnstore   indexn/a, columnstore   indexColumns with   column-based index: [member_no], [lastname], [firstname]

I hope this helps you when looking at your indexes!

Enjoy,
kt