NOTE: I’ve released other 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 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) and to a private email (thanks Vasco!), I have an updated version of sp_helpindex2:

For SQL Server 2005, here’s your new sp_helpindex2 script: sp_helpindex2_2005.zip (2.89 KB)

And, here’s a simple test script for 2005:

 

DROP TABLE tbl1
GO

 

CREATE TABLE tbl1( c1 int, c2 int, c3 int, c4 int)
GO
CREATE INDEX ix_1 ON tbl1(c1) INCLUDE (c2)
CREATE INDEX ix_2 ON tbl1(c1)
CREATE INDEX ix_3 ON tbl1(c1) INCLUDE (c2, c3)
CREATE INDEX ix_4 ON tbl1(c1, c3) INCLUDE (c2)
CREATE INDEX ix_5 ON tbl1(c3) INCLUDE (c1, c2, c4)
CREATE INDEX ix_6 ON tbl1(c1, c2) INCLUDE (c3, c4)
go

sp_helpindex2 tbl1
go

 

index_name index_description index_keys included_columns
ix_1 nonclustered located on fg1 c1 c2
ix_2 nonclustered located on fg1 c1 NULL
ix_3 nonclustered located on fg1 c1 c2, c3
ix_4 nonclustered located on fg1 c1, c3 c2
ix_5 nonclustered located on fg1 c3 c1, c2, c4
ix_6 nonclustered located on fg1 c1, c2 c3, c4

For SQL Server 2008, here’s your new sp_helpindex2 script: sp_helpindex2_2008.zip (2.84 KB)

And, here’s a simple test script for 2008:

 

DROP TABLE tbl1
GO

 

CREATE TABLE tbl1( c1 int, c2 int, c3 int, c4 int)
CREATE INDEX ix_1 ON tbl1(c1) INCLUDE (c2)
CREATE INDEX ix_2 ON tbl1(c1)
CREATE INDEX ix_3 ON tbl1(c1) INCLUDE (c2, c3)
CREATE INDEX ix_4 ON tbl1(c1, c3) INCLUDE (c2)
CREATE INDEX ix_5 ON tbl1(c3) INCLUDE (c1, c2, c4)
CREATE INDEX ix_6 ON tbl1(c1, c2) INCLUDE (c3, c4)

CREATE INDEX ix_1f ON tbl1(c1) INCLUDE (c2)
WHERE c3 IS NOT NULL

CREATE INDEX ix_2f ON tbl1(c1)
WHERE c4 > 2

 

CREATE INDEX ix_3f ON tbl1(c1) INCLUDE (c2, c3)
WHERE c4 > 2 AND c1 < 50 AND c2 = 12

 

CREATE INDEX ix_4f ON tbl1(c1, c3) INCLUDE (c2)
WHERE c4 IS NOT NULL AND c1 = 12

 

CREATE INDEX ix_5f ON tbl1(c3) INCLUDE (c1, c2, c4)
WHERE c1 > 5

CREATE INDEX ix_6f ON tbl1(c1, c2) INCLUDE (c3, c4)
WHERE c4 < 20
go

sp_helpindex2 tbl1
go

index_name index_description index_keys included_columns filter_definition
ix_1 nonclustered located on PRIMARY c1 c2 NULL
ix_1f nonclustered located on PRIMARY c1 c2 ([c3] IS NOT NULL)
ix_2 nonclustered located on PRIMARY c1 c2 NULL
ix_2f nonclustered located on PRIMARY c1 c2 ([c4]>(2))
ix_3 nonclustered located on PRIMARY c1 c2, c3 NULL
ix_3f nonclustered located on PRIMARY c1 c2, c3 ([c4]>(2) AND [c1]<(50) AND [c2]=(12))
ix_4 nonclustered located on PRIMARY c1, c3 c2 NULL
ix_4f nonclustered located on PRIMARY c1, c3 c2 ([c4] IS NOT NULL AND [c1]=(12))
ix_5 nonclustered located on PRIMARY c3 c1, c2, c4 NULL
ix_5f nonclustered located on PRIMARY c3 c1, c2, c4 ([c1]>(5))
ix_6 nonclustered located on PRIMARY c1, c2 c3, c4 NULL
ix_6f nonclustered located on PRIMARY c1, c2 c3, c4 ([c4]<(20))

Have fun!
kt