{"id":478,"date":"2011-02-27T11:45:00","date_gmt":"2011-02-27T11:45:00","guid":{"rendered":"\/blogs\/kimberly\/post\/sp_helpindex-rewrites.aspx"},"modified":"2017-02-27T22:50:50","modified_gmt":"2017-02-28T06:50:50","slug":"use-this-sp_helpindex-rewrites","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/use-this-sp_helpindex-rewrites\/","title":{"rendered":"(OLD): sp_helpindex rewrites"},"content":{"rendered":"<p><span style=\"font-family: verdana,geneva;\">I realize that I&#8217;ve created quite a few posts (and rewrites) around sp_helpindex and recently I&#8217;ve been recommending that most folks use my latest version (<em>jokingly, this is sp_helpindex9<\/em>) but it&#8217;s not so obvious where to get it and\/or what to do to install it. To make it easier&#8230; I&#8217;m just going to do a post like this EVERY TIME I have a new version and so there won&#8217;t be anything else to review\/read. <\/span><\/p>\n<p><span style=\"color: #ff6600;\">HERE IS A LINK TO THE NEW VERSION:<\/span> <a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/use-this-new-sql-server-2012-rewrite-for-sp_helpindex\/\">https:\/\/www.sqlskills.com\/blogs\/kimberly\/use-this-new-sql-server-2012-rewrite-for-sp_helpindex\/<\/a><\/p>\n<p><span style=\"font-family: verdana,geneva;\">All versions use a base procedure that builds the columns needed to produce the detailed output. So, you need to setup TWO procedures. <\/span><\/p>\n<p><strong>Step 1: setup sp_SQLskills_ExposeColsInIndexLevels<\/strong><\/p>\n<blockquote><p>Create this procedure first: <a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-content\/uploads\/2010\/5\/sp_sqlskills_exposecolsinindexlevels.sql\">sp_SQLskills_ExposeColsInIndexLevels.sql (6.73 kb)<\/a>.<\/p><\/blockquote>\n<blockquote><p><span style=\"font-family: verdana,geneva;\">This is what gives us the tree\/leaf definitions. And, this works for both SQL Server 2005 and SQL Server 2008. <\/span><\/p><\/blockquote>\n<p><strong>Step 2: setup the replacement procedure for sp_helpindex. This IS version specific:<\/strong><\/p>\n<blockquote><p>On SQL Server 200<strong><span style=\"text-decoration: underline;\">5<\/span><\/strong>, use: <a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-content\/uploads\/2010\/5\/sp_sqlskills_sql2005_helpindex.sql\">sp_SQLskills_SQL2005_helpindex.sql (10.50 kb)<\/a>\u00a0to create sp_SQLskills_SQL200<strong>5<\/strong>_helpindex.<\/p>\n<p>On SQL Server 200<strong><span style=\"text-decoration: underline;\">8<\/span><\/strong>, use: <a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-content\/uploads\/2010\/11\/sp_sqlskills_sql2008_helpindex%20(sp_helpindex9).sql\">sp_SQLskills_SQL2008_helpindex (sp_helpindex9).sql (11.06 kb)<\/a>\u00a0to create sp_SQLskills_SQL200<strong>8<\/strong>_helpindex.<\/p><\/blockquote>\n<p><strong>Step 3: 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 <a href=\"https:\/\/www.sqlskills.com\/blogs\/kimberly\/a-new-and-improved-sp_helpindex-jokingly-sp_helpindex8\/\" target=\"_blank\">here<\/a>.<\/p>\n<p>Enjoy!<br \/>\nKimberly<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I realize that I&#8217;ve created quite a few posts (and rewrites) around sp_helpindex and recently I&#8217;ve been recommending that most folks use my latest version (jokingly, this is sp_helpindex9) but it&#8217;s not so obvious where to get it and\/or what to do to install it. To make it easier&#8230; I&#8217;m just going to do a [&hellip;]<\/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,48,63,65,66,67],"tags":[],"class_list":["post-478","post","type-post","status-publish","format-standard","hentry","category-clustered-index","category-clustering-key","category-filtered-indexes","category-indexes","category-nonclustered-indexes","category-sp_helpindex-rewrites","category-sql-server-2005","category-sql-server-2008","category-sql-server-2008-r2"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/478","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=478"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/478\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=478"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=478"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=478"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}