(OLD): sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex

 

IMPORTANT NOTE: Always 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 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) OR the density vector (which only shows the densities (or averages) for the left-based subsets of the key). 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) OR you can run queries to find the included columns for a given index:

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

but, there isn’t a nice clean way to show all of the included columns for all indexes for a particular table… until now :)A couple of weeks ago I sat down and rewrote sp_helpindex. I was actually on a plane from Hyderabad to Frankfurt or from Frankfurt to San Fran or from San Fran to Seattle (it was a long day :) and I was using (and well, forcing myself to learn how to use :) my new 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!!!) 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) but the laptops were two Lenovos – one Lenovo (the T60p) was purchased 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 17 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…). 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 back I felt like I really needed to get my money’s worth so I just *had* to work on the flights home (ah, security with *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).

OK – so back to the story… I 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: sp_helpindex2_2005.zip (2.71 KB).

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 Miracle Iceland) and we’re teaching “the Accidental DBA” (this past Monday) 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 :), I had time to rewrite sp_helpindex… again. And, here’s what I ended up with…

 

exec sp_helpindex2 ‘member’

 

index_name index_description index_keys included_columns filter_definition
member_corporation_link nonclustered located on PRIMARY corp_no NULL NULL
member_ident clustered, unique, primary key located on PRIMARY member_no NULL NULL
member_region_link nonclustered located on PRIMARY region_no NULL NULL
NCIndexCoveringLnFnMiIncludePhone nonclustered located on PRIMARY lastname, firstname, middleinitial phone_no NULL
NCIndexCoversAll4Cols nonclustered located on PRIMARY lastname, firstname, middleinitial, phone_no NULL NULL
NCIndexLNinKeyInclude3OtherCols nonclustered located on PRIMARY lastname firstname, middleinitial, phone_no NULL
NCIndexLNOnly nonclustered located on PRIMARY lastname NULL NULL
QuickFilterTest nonclustered located on PRIMARY lastname phone ([lastname]>’S’ AND [lastname]<‘T’)

 

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 code but it’s not my worst code either” <G>. And, so, here it is: sp_helpindex2_2008.zip (2.75 KB).

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.

Have fun!
kt

8 thoughts on “(OLD): sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex

  1. "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."

    I think I’m misunderstanding your statement. If I create a proc that starts with "sp_", are you saying that in 2005 & 2008, I can’t execute it from other databases?

    Thanks,
    Morgan

    The following works with 2005 SP1 & 2005 SP2
    ———————————————-

    — Create proc in master
    use master
    go

    if object_id(‘dbo.sp_test’) is not null
    drop proc dbo.sp_test
    go

    create proc dbo.sp_test
    as
    select db_name() as DBName
    go

    — test it in master
    exec dbo.sp_test
    go

    — create proc with same name in other db
    use AdventureWorks
    go

    if object_id(‘dbo.sp_test’) is not null
    drop proc dbo.sp_test
    go

    create proc dbo.sp_test
    as
    select ‘aw’ as test
    go

    — test it to see if it take precedence over the one in master
    exec dbo.sp_test
    go

    — test proc in another db to see if it uses the one in master
    use model
    go

    exec dbo.sp_test
    go

    — test proc in another db to see if it uses the one in master
    use tempdb
    go

    exec dbo.sp_test
    go

    — Clean up
    use master
    go

    if object_id(‘dbo.sp_test’) is not null
    drop proc dbo.sp_test
    go

    use AdventureWorks
    go

    if object_id(‘dbo.sp_test’) is not null
    drop proc dbo.sp_test
    go

  2. Hi Morgan

    Here’s what Kim means… although your test works, and you can access your sp_test from anywhere, Kim’s proc will not work because it accesses system objects. If instead of just SELECT db_name() in your proc, you had SELECT count(*) FROM sys.objects, you would always get the number of objects in master, even though you called the proc from tempdb, or model, or AW.

    Once you us sp_MSmarksystemobject, then the proc will access the objects from the local database.

    Since Kim accesses sys.tables and sys.indexes, as well as others, her proc would not work without marking it as system.

    ~Kalen

  3. Hi Kimberley,
    Since long I have been a huge fan of yours. I have learned a lot from the 10 part series on SQL Server 2005 Best Practices. Coming to the above script, I have found this extremely useful. I added an additional functionality to this code, so that it also displays the Index Size (used the dpages column of the sysindexes table). Thus along with the included columns my sp_helpindex2 also displays the size of the Index.
    Once again thanks for the script..
    Keep Posting……

  4. Hey Everyone –

    Found a few bugs in the script. I’ve notified Kimberley, but until she updates it, here’s what you can do to fix it yourselves…

    Basically, I null out the @inc_columns every loop, I wrap the "if @inc_count > 0" section in a begin/end block (it’s obvious; she indented it but didn’t block it), and I clear out the #IncludedColumns table after we’ve figured out what each column gets (as part of the above begin/end block)

    Hope that helps anyone using this :)

  5. A side effect of the sys.sp_MS_marksystemobject sp… is that you can’t see the stored proc (like sp_helpindex2) in the SSMS 2008. Is there a way around that?

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Wow! Wow! Wow! THANK YOU!

I announced my retirement from SQL/tech here and your comments on my blog, on LinkedIn, and on Facebook were overwhelming and humbling! I’m so touched

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.