{"id":2819,"date":"2017-02-27T23:02:40","date_gmt":"2017-02-28T07:02:40","guid":{"rendered":"http:\/\/3.209.169.194\/blogs\/kimberly\/?p=2819"},"modified":"2021-11-11T15:47:30","modified_gmt":"2021-11-11T23:47:30","slug":"sp_helpindex-v20170228","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/sp_helpindex-v20170228\/","title":{"rendered":"(OLD): sp_helpindex"},"content":{"rendered":"\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t
\n\t\t\t
\n\t\t\t\t\t\t\t
\n\t\t\t\t\t\t
\n\t\t\t\t
\n\t\t\t\t\t\t\t\t
\n\t\t\t\t

IMPORTANT UPDATE:<\/h2>

PLEASE REVIEW THIS POST FOR THE UPDATED SCRIPTS: USE THESE: Updates to SQLskills Index Procedures<\/a><\/p>

****************************************************<\/p>

Since I\u2019ve rewritten sp_helpindex a few times, I have a few blogs posts in this category. Each time I do an update I\u2019ll make the NEW version titled USE THIS and I’ll change all others to (OLD).<\/p>

The point of this updated version of sp_helpindex is to add details for newer functionality (included columns and filters) as well as better describe what’s REALLY in your indexes (at the leaf level AND up the b-tree). This particular version won’t error for new index types AND it’s “generic” and not version specific (meaning you don’t have to have an sp_SQLskills_SQL2012_helpindex and instead just have one sp_SQLskills_helpindex across versions). You use sp_SQLskills_helpindex in exactly the same way as sp_helpindex; if you can pass it into sp_helpindex then you can pass it into sp_SQLskills_helpindex!<\/p>

EXAMPLES:<\/p>

sp_helpindex tablename <\/span><\/strong>WORKS<\/pre>
sp_helpindex owner.tablename<\/span><\/strong> DOES NOT WORK<\/pre>
sp_helpindex 'owner.tablename'<\/span><\/strong> WORKS<\/pre>
To setup this\u00a0version of sp_helpindex, you need TWO scripts. Both scripts are generic and work on all versions from SQL Server 2005\u00a0through SQL Server 2016. To produce the detailed output, you need to first create these system procedures.<\/p>
Step 1: Setup sp_SQLskills_ExposeColsInIndexLevels<\/h2>
Create this procedure first:<\/strong> sp_SQLskills_ExposeColsInIndexLevels.sql<\/a>\u00a0 (7 kb – rename download to .sql).<\/p>
This is what gives us the tree\/leaf definitions.<\/p>
\u00a0<\/h2>
Step 2: Setup the replacement procedure for sp_helpindex -> sp_SQLskills_helpindex<\/h2>
Create the new sp_helpindex<\/strong>, use: sp_SQLskills_helpindex.sql<\/a>\u00a0 (18 kb – rename download to .sql) to create sp_SQLskills_helpindex.<\/p>
Have fun! And, post a comment and\/or shoot me an email if you find any issues!<\/p>
Thanks for reading,
Kimberly<\/p>
\u00a0<\/h2>\t\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t<\/div>\n\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/div>\n\t\t\t\t\t\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t<\/section>\n\t\t\t\t\t\t\t\t\t<\/div>\n\t\t\t<\/div>\n\t\t\t\t\t<\/div>\n\t\t","protected":false},"excerpt":{"rendered":"
IMPORTANT UPDATE: PLEASE REVIEW THIS POST FOR THE UPDATED SCRIPTS: USE THESE: Updates to SQLskills Index Procedures **************************************************** Since I\u2019ve rewritten sp_helpindex a few times, I have a few blogs posts in this category. Each time I do an update I\u2019ll make the NEW version titled USE THIS and I’ll change all others to (OLD). […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36,63,79],"tags":[],"class_list":["post-2819","post","type-post","status-publish","format-standard","hentry","category-indexes","category-sp_helpindex-rewrites","category-tools"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2819","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=2819"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/2819\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=2819"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=2819"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=2819"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}