{"id":578,"date":"2008-04-02T18:22:00","date_gmt":"2008-04-02T18:22:00","guid":{"rendered":"\/blogs\/kimberly\/post\/sp_helpindex2-to-show-included-columns-(20052b)-and-filtered-indexes-(2008)-which-are-not-shown-by-sp_helpindex.aspx"},"modified":"2017-02-27T22:53:51","modified_gmt":"2017-02-28T06:53:51","slug":"sp_helpindex2-to-show-included-columns-2005-and-filtered-indexes-2008-which-are-not-shown-by-sp_helpindex","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/sp_helpindex2-to-show-included-columns-2005-and-filtered-indexes-2008-which-are-not-shown-by-sp_helpindex\/","title":{"rendered":"(OLD): sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex"},"content":{"rendered":"

 <\/p>\n

IMPORTANT NOTE:\u00a0Always check the sp_helpindex rewrites<\/a> category for the USE THIS<\/strong> post!<\/span><\/p>\n

OK – so this has been frustrating me for many months… when you create indexes with included columns (which was a new feature of SQL Server 2005), they’re not shown by sp_helpindex or by DBCC SHOW_STATISTICS. I understand this not showing for statistics because included columns are not factored into the histogram (that’s only the high order element which is the first column in the index<\/em>) OR the density vector (which only shows the densities (or averages) for the left-based subsets of the key<\/em>). So, why doesn’t sp_helpindex show it? Well… I guess it just didn’t get updated for SQL 2005. So, in SQL 2008, I was hoping I’d not only see included columns but also filtered indexes… well, neither is there and sp_helpindex is still the same old proc. Don’t get me wrong, you can use SSMS to see all of the index properties for a single index (pane, by pane for each property<\/em>) OR you can run queries to find the included columns for a given index:<\/p>\n

SELECT
\n<\/span>(<\/span>CASE<\/span> ic<\/span>.<\/span>key_ordinal\u00a0<\/span>WHEN<\/span> 0\u00a0<\/span>THEN<\/span> CAST<\/span>(<\/span>1\u00a0<\/span>AS<\/span> tinyint<\/span>)<\/span>\u00a0<\/span>ELSE<\/span> ic<\/span>.<\/span>key_ordinal <\/span>END<\/span>)<\/span> AS<\/span> [ID]<\/span>,
\n<\/span>clmns<\/span>.<\/span>name<\/span> AS<\/span> [Name]<\/span>,
\n<\/span>CAST<\/span>(<\/span>COLUMNPROPERTY<\/span>(<\/span>ic<\/span>.<\/span>object_id<\/span>,<\/span> clmns<\/span>.<\/span>name<\/span>,<\/span> N<\/span>‘IsComputed’<\/span>)<\/span> AS<\/span> bit<\/span>)<\/span> AS<\/span> [IsComputed]<\/span>,
\n<\/span>ic<\/span>.<\/span>is_descending_key <\/span>AS<\/span> [Descending]<\/span>,
\n<\/span>ic<\/span>.<\/span>is_included_column <\/span>AS<\/span> [IsIncluded]
\n<\/span>FROM <\/span>sys.tables<\/span> AS<\/span> tbl
\n<\/span>INNER<\/span> JOIN<\/span> sys.indexes<\/span> AS<\/span> i
\n<\/span>ON<\/span> (<\/span>i<\/span>.<\/span>index_id <\/span>><\/span> 0\u00a0<\/span>AND<\/span> i<\/span>.<\/span>is_hypothetical <\/span>=<\/span> 0<\/span>)<\/span> AND<\/span> (<\/span>i<\/span>.<\/span>object_id <\/span>= <\/span>tbl<\/span>.<\/span>object_id<\/span>)
\nINNER<\/span> JOIN<\/span> sys.index_columns<\/span> AS<\/span> ic
\n<\/span>ON<\/span> (<\/span>ic<\/span>.<\/span>column_id <\/span>><\/span> 0\u00a0<\/span>AND<\/span> (<\/span>ic<\/span>.<\/span>key_ordinal <\/span>><\/span> 0\u00a0<\/span>OR<\/span> ic<\/span>.<\/span>partition_ordinal <\/span>=<\/span> 0\u00a0<\/span>OR<\/span> ic<\/span>.<\/span>is_included_column <\/span>!=<\/span> 0<\/span>))<\/span>
\n<\/span>AND<\/span> (<\/span>ic<\/span>.<\/span>index_id <\/span>= <\/span>CAST<\/span>(<\/span>i<\/span>.<\/span>index_id <\/span>AS<\/span> int<\/span>)<\/span> AND<\/span> ic<\/span>.<\/span>object_id <\/span>= <\/span>i<\/span>.<\/span>object_id<\/span>)
\nINNER<\/span> JOIN<\/span> sys.columns<\/span> AS<\/span> clmns
\n<\/span>ON<\/span> clmns<\/span>.<\/span>object_id<\/span> =<\/span> ic<\/span>.<\/span>object_id<\/span>\u00a0<\/span>AND<\/span> clmns<\/span>.<\/span>column_id <\/span>=<\/span> ic<\/span>.<\/span>column_id
\n<\/span>WHERE <\/span>(<\/span>i<\/span>.<\/span>name <\/span>= <\/span>N<\/span>‘[MyIndex]’<\/span>)\u00a0AND ((<\/span>tbl<\/span>.<\/span>name <\/span>= <\/span>N<\/span>‘[MyTable]’<\/span>\u00a0<\/span>AND<\/span> SCHEMA_NAME<\/span>(<\/span>tbl<\/span>.<\/span>schema_id<\/span>) = <\/span>N<\/span>‘[MySchema]’<\/span>))
\n<\/span>ORDER<\/span> BY<\/span> IsIncluded<\/span>,<\/span> [ID] <\/span>ASC<\/span><\/p><\/blockquote>\n

but, there isn’t a nice clean way to show all of the included columns for all indexes for a particular table… until now :)<\/span>A couple of weeks ago I sat down and rewrote sp_helpindex. I was actually on\u00a0a plane from Hyderabad to Frankfurt\u00a0or\u00a0from Frankfurt to San Fran or from San Fran to Seattle (it was a long day <\/span><\/em>:) and I was using (and well, forcing myself to learn how to use <\/span><\/em>:) my new<\/span><\/strong> Vista laptop. OK, that’s a HUGE story in and of itself and it definitely warrants its own post but I’ll sum up the story with the fact that I had to purchase a new laptop while in Hyderabad because BOTH my primary laptop (T61p) AND my backup laptop (T60p) BOTH (yes, BOTH!!!<\/span><\/strong><\/em>) suffered catastrophic disk failures on their boot drives within 24 hours of each other. In the end, I really cannot believe the “coincidence” of two laptops crashing within 24 hours of each other. Yes, I thought MTBF too (at first<\/span><\/em>) but the laptops were two Lenovos – one Lenovo (the T60p) was\u00a0purchased in Feb 2007 and the second, a Lenovo T61p was purchase in Oct 2007. And, it was the T61p that went first. The only thing I can even begin to speculate about and\/or think to attribute it to (as I was in India for\u00a017 days from Mar 3 through Mar 20 and this all started on Mar 17) was an overactive metal detector at the hotel at which I was staying (or something related to St. Patrick but I’ve since ruled that out – and no, I wasn’t drinking green beer either…<\/span><\/em>). OK, I really need to do another post to give you all of the details about this trip BUT, I did get a new laptop… and, having just bought it only shortly before I flew\u00a0back I felt like I really needed to get my money’s worth so I just *had* to work on the flights home\u00a0(ah, security\u00a0with *3* laptops was NOT fun and I’m *VERY* glad that none of them asked me to “boot” my laptops to prove they were working… that could have been a VERY bad situation… lol<\/span><\/em>).<\/span><\/p>\n

OK – so back to the story…\u00a0I was working on the flights and I was preparing to deliver some content on the Friday after I returned (yes, I taught a full day in India on Wednesday then flew back leaving India at 2:15am Thursday morning so that I could arrive back in Redmond at roughly 7pm Thursday night – about 30 hours later – and then teach Friday morning for an 8:30 start time… ah, I was *really* tired on Friday night :). Anyway, in preparing, I decided that I finally needed to re-write sp_helpindex. When I was first writing it, I was only thinking of SQL Server 2005. So, here’s the 2005 version that I wrote:\u00a0sp_helpindex2_2005.zip (2.71 KB)<\/a>.<\/p>\n

So, I had wanted to blog that when I got back to Redmond but in preparing for the trip we’re on now AND rebuilding my primary and backup laptops, well, it got tabled. So now, today, Paul and I are in Iceland (working with our great friends at <\/em>Miracle Iceland<\/a>) and we’re teaching “the Accidental DBA” (this past Monday<\/em>) and SQL Server 2008 New Features in Database Infrastructure and Scalability (Tue through Thursday)… I was giving a lecture on Filtered Indexes in SQL Server 2008 and I, once again, found myself needing a better sp_helpindex. So, when Paul got up to talk about Compression (which is no short lecture for him <\/em>:), I had time to rewrite sp_helpindex… again. And, here’s what I\u00a0ended up with…<\/p>\n

 <\/p>\n

exec sp_helpindex2 ‘member’ <\/span><\/span><\/p>\n

 <\/p>\n\n\n\n\n\n\n\n\n\n\n\n
index_name<\/strong><\/span><\/td>\nindex_description<\/strong><\/span><\/td>\nindex_keys<\/strong><\/span><\/td>\nincluded_columns<\/strong><\/span><\/td>\nfilter_definition<\/span><\/strong><\/td>\n<\/tr>\n
member_corporation_link<\/span><\/td>\nnonclustered located on PRIMARY<\/span><\/td>\ncorp_no<\/span><\/td>\nNULL<\/span><\/td>\nNULL<\/span><\/td>\n<\/tr>\n
member_ident<\/span><\/td>\nclustered, unique, primary key located on PRIMARY<\/span><\/td>\nmember_no<\/span><\/td>\nNULL<\/span><\/td>\nNULL<\/span><\/td>\n<\/tr>\n
member_region_link<\/span><\/td>\nnonclustered located on PRIMARY<\/span><\/td>\nregion_no<\/span><\/td>\nNULL<\/span><\/td>\nNULL<\/span><\/td>\n<\/tr>\n
NCIndexCoveringLnFnMiIncludePhone<\/span><\/td>\nnonclustered located on PRIMARY<\/span><\/td>\nlastname, firstname, middleinitial<\/span><\/td>\nphone_no<\/span><\/td>\nNULL<\/span><\/td>\n<\/tr>\n
NCIndexCoversAll4Cols<\/span><\/td>\nnonclustered located on PRIMARY<\/span><\/td>\nlastname, firstname, middleinitial, phone_no<\/span><\/td>\nNULL<\/span><\/td>\nNULL<\/span><\/td>\n<\/tr>\n
NCIndexLNinKeyInclude3OtherCols<\/span><\/td>\nnonclustered located on PRIMARY<\/span><\/td>\nlastname<\/span><\/td>\nfirstname, middleinitial, phone_no<\/span><\/td>\nNULL<\/span><\/td>\n<\/tr>\n
NCIndexLNOnly<\/span><\/td>\nnonclustered located on PRIMARY<\/span><\/td>\nlastname<\/span><\/td>\nNULL<\/span><\/td>\nNULL<\/span><\/td>\n<\/tr>\n
QuickFilterTest<\/span><\/td>\nnonclustered located on PRIMARY<\/span><\/td>\nlastname<\/span><\/td>\nphone<\/span><\/td>\n([lastname]>’S’ AND [lastname]<‘T’)<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

 <\/p>\n

So, in the end, I can quickly see whether or not my index has included_columns and\/or a filter_definition. Don’t get me wrong, these indexes above are NOT necessarily a good combination of indexes (or recommendation of ANY kind) to have – these were just created to make sure that my code works. And, as my good friend Gunnar would say – “it’s not my best <\/em>code but it’s not my worst <\/em>code either” <G>. And, so, here it is: sp_helpindex2_2008.zip (2.75 KB)<\/a>. <\/span><\/p>\n

Pretty darn useful for sure! Oh, and I used the undoc’ed sp_MS_marksystemobject so that I could still create the sp_ in master but then execute it in all other databases. It’s frustrating that this behavior (with sp_ named objects) no longers works in 2005\/2008 but at least the sp_MS_marksystemobject still sets the behavior so that we can create this one proc in master but use it in all other databases. <\/span><\/p>\n

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

  IMPORTANT NOTE:\u00a0Always check the sp_helpindex rewrites category for the USE THIS post! OK – so this has been frustrating me for many months… when you create indexes with included columns (which was a new feature of SQL Server 2005), they’re not shown by sp_helpindex or by DBCC SHOW_STATISTICS. I understand this not showing for […]<\/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-578","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\/578","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=578"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/posts\/578\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/media?parent=578"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/categories?post=578"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/kimberly\/wp-json\/wp\/v2\/tags?post=578"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}