Recently I have made my self aware to the old fact that when I tune a query, I always use the same 3 procedures over and over again: sp_helpindex, sp_columns and sp_spaceused. Plus, I always use SSMS to script the table schema to see if there are any Included Columns which are not part of the output provided by sp_helpindex.
I guess I had the time and feel to address this issue and come up with something more efficient that saves me the hassle of typing these 3 procedures over and over again, and also eliminates the need to use SSMS to script the table schema for the sake of the Included Columns.
I created sp_helpindex2 which outputs 2 result sets.
The first result set provides index information containing the Included Columns as well as the table level information similar to the information provided by sp_spaceused.
The second result set provides column information and in fact it is the output of sp_columns straight forward with no tweaks.
The procedure accepts 3 input parameters:
The first one @Table is simply the table name we work on.
The second parameter @IndexExtendedinfo has a default value of 0 (false) so that only if you pass a value of 1 (true) it takes effect and adds additional information to the first result set. The information added is the following 3 columns from the system function sys.dm_db_index_physical_stats: avg_fragmentation_in_percent, page_count and partition_number. Note that setting this parameter to true increases the duration.
The third parameter @ColumnsInfo has a default value of 1 (true) and provides the second result set.
sp_helpindex2 is an improvement to the original procedure which I find very practical and useful in my daily work, try it out and let me get your comments.
Feel free to modify the procedures and add or remove columns from the output to best fit your needs.
Download the script here:
Comments