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.<\/p>\n
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.<\/p>\n
Create this procedure first:<\/strong> sp_SQLskills_ExposeColsInIndexLevels.sql<\/a> (7 kb).<\/p>\n This is what gives us the tree\/leaf definitions. And, this works for all versions: 2005, 2008\/R2, and 2012.<\/p>\n On SQL Server 2005<\/strong>, use: sp_SQLskills_SQL2005_helpindex.sql<\/a> (11 kb) to create sp_SQLskills_SQL2005_helpindex.<\/p>\n On SQL Server 2008<\/strong>, use: sp_SQLskills_SQL2008_helpindex.sql<\/a> (12 kb) to create sp_SQLskills_SQL2008_helpindex. (NOTE:<\/strong> This does run on SQL Server 2012 but if your table has a columnstore index, it will generate an error.)<\/p>\n On SQL Server 2012<\/strong>, use: sp_SQLskills_SQL2012_helpindex.sql<\/a> (12 kb) to create sp_SQLskills_SQL2012_helpindex.<\/p>\n Optionally<\/strong>, 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<\/a>.<\/p>\n On SQL Server 2012, the output will look like the following (index_id 5 is a columnstore index):<\/p>\n I hope this helps you when looking at your indexes!<\/p>\n Enjoy, 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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[16,17,30,36,63],"tags":[],"class_list":["post-2622","post","type-post","status-publish","format-standard","hentry","category-clustered-index","category-clustering-key","category-filtered-indexes","category-indexes","category-sp_helpindex-rewrites"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2622","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/comments?post=2622"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2622\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=2622"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=2622"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=2622"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}Step 2: Setup the replacement procedure for sp_helpindex. This IS version specific:<\/h2>\n
Step 3: Setup a hot-key combination<\/h2>\n
<\/h2>\n
The Output<\/h2>\n
\n
\n index_id<\/td>\n is_disabled<\/td>\n index_name<\/td>\n index_description<\/td>\n index_keys<\/td>\n included_columns<\/td>\n filter_definition<\/td>\n columns_in_tree<\/td>\n columns_in_leaf<\/td>\n<\/tr>\n \n 1<\/td>\n 0<\/td>\n [member_ident]<\/td>\n clustered, unique, \u00a0 primary key located on PRIMARY<\/td>\n [member_no]<\/td>\n NULL<\/td>\n NULL<\/td>\n [member_no]<\/td>\n All columns \u00a0 “included” – the leaf level IS the data row.<\/td>\n<\/tr>\n \n 2<\/td>\n 0<\/td>\n [member_corporation_link]<\/td>\n nonclustered located \u00a0 on PRIMARY<\/td>\n [corp_no]<\/td>\n NULL<\/td>\n NULL<\/td>\n [corp_no], \u00a0 [member_no]<\/td>\n [corp_no], \u00a0 [member_no]<\/td>\n<\/tr>\n \n 3<\/td>\n 0<\/td>\n [member_region_link]<\/td>\n nonclustered located \u00a0 on PRIMARY<\/td>\n [region_no]<\/td>\n NULL<\/td>\n NULL<\/td>\n [region_no], \u00a0 [member_no]<\/td>\n [region_no], \u00a0 [member_no]<\/td>\n<\/tr>\n \n 4<\/td>\n 0<\/td>\n [LastNameInd]<\/td>\n nonclustered located \u00a0 on PRIMARY<\/td>\n [lastname]<\/td>\n NULL<\/td>\n NULL<\/td>\n [lastname], \u00a0 [member_no]<\/td>\n [lastname], \u00a0 [member_no]<\/td>\n<\/tr>\n \n 5<\/td>\n 0<\/td>\n [columnstore_index]<\/td>\n nonclustered \u00a0 columnstore located on PRIMARY<\/td>\n n\/a, see \u00a0 columns_in_leaf for details<\/td>\n n\/a, columnstore \u00a0 index<\/td>\n n\/a, columnstore \u00a0 index<\/td>\n n\/a, columnstore \u00a0 index<\/td>\n Columns with \u00a0 column-based index: [member_no], [lastname], [firstname]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\nkt<\/p>\n","protected":false},"excerpt":{"rendered":"