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_id is_disabled index_name index_description index_keys included_columns filter_definition columns_in_tree columns_in_leaf
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!

Enjoy,
kt