{"id":567,"date":"2008-08-24T19:23:00","date_gmt":"2008-08-24T19:23:00","guid":{"rendered":"\/blogs\/kimberly\/post\/Updates-(fixes)-to-sp_helpindex2.aspx"},"modified":"2017-02-27T22:51:46","modified_gmt":"2017-02-28T06:51:46","slug":"updates-fixes-to-sp_helpindex2","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/updates-fixes-to-sp_helpindex2\/","title":{"rendered":"(OLD): Updates (fixes) to sp_helpindex2"},"content":{"rendered":"

NOTE: <\/strong>I’ve released\u00a0other versions of sp_helpindex since this post. Check out the category: sp_helpindex rewrites<\/a> for all of the options!<\/p><\/blockquote>\n

I first posted an update to sp_helpindex here. My version of sp_helpindex was solely to expand what sp_helpindex showed and adds 1 or 2 things based on version: for SQL2005+ it adds included columns and for SQL2008 it also adds the filter predicate. So, there were two versions of sp_helpindex2 depending on which verison you’re using. A lot of folks like the changes to this sp but, alas, it had a bug (or two :) and in fact, I found a few others when I went back over this as well. So, thanks to Josh (who commented here<\/a>) and to a private email (thanks Vasco!), I have an updated version of sp_helpindex2:<\/p>\n

For SQL Server 2005, here’s your new sp_helpindex2 script: sp_helpindex2_2005.zip<\/a> (2.89 KB) <\/span><\/span><\/p>\n

And, here’s a simple test script for 2005:<\/p>\n

 <\/p>\n

DROP TABLE tbl1
\nGO <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

 <\/p>\n

CREATE TABLE tbl1( c1 int, c2 int, c3 int, c4 int)
\nGO
\nCREATE INDEX ix_1 ON tbl1(c1) INCLUDE (c2)
\nCREATE INDEX ix_2 ON tbl1(c1)
\nCREATE INDEX ix_3 ON tbl1(c1) INCLUDE (c2, c3)
\nCREATE INDEX ix_4 ON tbl1(c1, c3) INCLUDE (c2)
\nCREATE INDEX ix_5 ON tbl1(c3) INCLUDE (c1, c2, c4)
\nCREATE INDEX ix_6 ON tbl1(c1, c2) INCLUDE (c3, c4)
\ngo <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

sp_helpindex2 tbl1
\ngo <\/span><\/span><\/p>\n

 <\/p>\n\n\n\n\n\n\n\n\n\n
index_name <\/span><\/td>\nindex_description <\/span><\/td>\nindex_keys <\/span><\/td>\nincluded_columns <\/span><\/td>\n<\/tr>\n
ix_1 <\/span><\/td>\nnonclustered located on fg1 <\/span><\/td>\nc1 <\/span><\/td>\nc2 <\/span><\/td>\n<\/tr>\n
ix_2 <\/span><\/td>\nnonclustered located on fg1 <\/span><\/td>\nc1 <\/span><\/td>\nNULL <\/span><\/td>\n<\/tr>\n
ix_3 <\/span><\/td>\nnonclustered located on fg1 <\/span><\/td>\nc1 <\/span><\/td>\nc2, c3 <\/span><\/td>\n<\/tr>\n
ix_4 <\/span><\/td>\nnonclustered located on fg1 <\/span><\/td>\nc1, c3 <\/span><\/td>\nc2 <\/span><\/td>\n<\/tr>\n
ix_5 <\/span><\/td>\nnonclustered located on fg1 <\/span><\/td>\nc3 <\/span><\/td>\nc1, c2, c4 <\/span><\/td>\n<\/tr>\n
ix_6 <\/span><\/td>\nnonclustered located on fg1 <\/span><\/td>\nc1, c2 <\/span><\/td>\nc3, c4 <\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

For SQL Server 2008, here’s your new sp_helpindex2 script: sp_helpindex2_2008.zip<\/a>\u00a0(2.84 KB) <\/span><\/span><\/p>\n<\/blockquote>\n

And, here’s a simple test script for 2008:<\/p>\n

 <\/p>\n

DROP TABLE tbl1
\nGO <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

 <\/p>\n

CREATE TABLE tbl1( c1 int, c2 int, c3 int, c4 int)
\nCREATE INDEX ix_1 ON tbl1(c1) INCLUDE (c2)
\nCREATE INDEX ix_2 ON tbl1(c1)
\nCREATE INDEX ix_3 ON tbl1(c1) INCLUDE (c2, c3)
\nCREATE INDEX ix_4 ON tbl1(c1, c3) INCLUDE (c2)
\nCREATE INDEX ix_5 ON tbl1(c3) INCLUDE (c1, c2, c4)
\nCREATE INDEX ix_6 ON tbl1(c1, c2) INCLUDE (c3, c4) <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

CREATE INDEX ix_1f ON tbl1(c1) INCLUDE (c2)
\nWHERE c3 IS NOT NULL
\n
\nCREATE INDEX ix_2f ON tbl1(c1)
\nWHERE c4 > 2<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

 <\/p>\n

CREATE INDEX ix_3f ON tbl1(c1) INCLUDE (c2, c3)
\nWHERE c4 > 2 AND c1 < 50 AND c2 = 12<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

 <\/p>\n

CREATE INDEX ix_4f ON tbl1(c1, c3) INCLUDE (c2)
\nWHERE c4 IS NOT NULL AND c1 = 12<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

 <\/p>\n

CREATE INDEX ix_5f ON tbl1(c3) INCLUDE (c1, c2, c4)
\nWHERE c1 > 5<\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

CREATE INDEX ix_6f ON tbl1(c1, c2) INCLUDE (c3, c4)
\nWHERE c4 < 20
\ngo <\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/span><\/p>\n

sp_helpindex2 tbl1
\ngo <\/span><\/span><\/p><\/blockquote>\n

\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
index_name <\/span><\/td>\nindex_description <\/span><\/td>\nindex_keys <\/span><\/td>\nincluded_columns <\/span><\/td>\nfilter_definition <\/span><\/td>\n<\/tr>\n
ix_1 <\/span><\/td>\nnonclustered located on PRIMARY <\/span><\/td>\nc1 <\/span><\/td>\nc2 <\/span><\/td>\nNULL <\/span><\/td>\n<\/tr>\n
ix_1f <\/span><\/td>\nnonclustered located on PRIMARY <\/span><\/td>\nc1 <\/span><\/td>\nc2 <\/span><\/td>\n([c3] IS NOT NULL) <\/span><\/td>\n<\/tr>\n
ix_2 <\/span><\/td>\nnonclustered located on PRIMARY <\/span><\/td>\nc1 <\/span><\/td>\nc2 <\/span><\/td>\nNULL <\/span><\/td>\n<\/tr>\n
ix_2f <\/span><\/td>\nnonclustered located on PRIMARY <\/span><\/td>\nc1 <\/span><\/td>\nc2 <\/span><\/td>\n([c4]>(2)) <\/span><\/td>\n<\/tr>\n
ix_3 <\/span><\/td>\nnonclustered located on PRIMARY <\/span><\/td>\nc1 <\/span><\/td>\nc2, c3 <\/span><\/td>\nNULL <\/span><\/td>\n<\/tr>\n
ix_3f <\/span><\/td>\nnonclustered located on PRIMARY <\/span><\/td>\nc1 <\/span><\/td>\nc2, c3 <\/span><\/td>\n([c4]>(2) AND [c1]<(50) AND [c2]=(12)) <\/span><\/td>\n<\/tr>\n
ix_4 <\/span><\/td>\nnonclustered located on PRIMARY <\/span><\/td>\nc1, c3 <\/span><\/td>\nc2 <\/span><\/td>\nNULL <\/span><\/td>\n<\/tr>\n
ix_4f <\/span><\/td>\nnonclustered located on PRIMARY <\/span><\/td>\nc1, c3 <\/span><\/td>\nc2 <\/span><\/td>\n([c4] IS NOT NULL AND [c1]=(12)) <\/span><\/td>\n<\/tr>\n
ix_5 <\/span><\/td>\nnonclustered located on PRIMARY <\/span><\/td>\nc3 <\/span><\/td>\nc1, c2, c4 <\/span><\/td>\nNULL <\/span><\/td>\n<\/tr>\n
ix_5f <\/span><\/td>\nnonclustered located on PRIMARY <\/span><\/td>\nc3 <\/span><\/td>\nc1, c2, c4 <\/span><\/td>\n([c1]>(5)) <\/span><\/td>\n<\/tr>\n
ix_6 <\/span><\/td>\nnonclustered located on PRIMARY <\/span><\/td>\nc1, c2 <\/span><\/td>\nc3, c4 <\/span><\/td>\nNULL <\/span><\/td>\n<\/tr>\n
ix_6f <\/span><\/td>\nnonclustered located on PRIMARY <\/span><\/td>\nc1, c2 <\/span><\/td>\nc3, c4 <\/span><\/td>\n([c4]<(20)) <\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/blockquote>\n

Have fun!
\nkt<\/p>\n","protected":false},"excerpt":{"rendered":"

NOTE: I’ve released\u00a0other versions of sp_helpindex since this post. Check out the category: sp_helpindex rewrites for all of the options! I first posted an update to sp_helpindex here. My version of sp_helpindex was solely to expand what sp_helpindex showed and adds 1 or 2 things based on version: for SQL2005+ it adds included columns and […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36,58,63,65,66],"tags":[],"class_list":["post-567","post","type-post","status-publish","format-standard","hentry","category-indexes","category-resources","category-sp_helpindex-rewrites","category-sql-server-2005","category-sql-server-2008"],"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/567","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=567"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/567\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=567"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=567"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=567"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}