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 WHEN 0 THEN CAST(1 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 > 0 AND i.is_hypothetical = 0) AND (i.object_id = tbl.object_id)
INNER JOIN sys.index_columns AS ic
ON (ic.column_id > 0 AND (ic.key_ordinal > 0 OR ic.partition_ordinal = 0 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”
"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
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
Thank you Kalen! Told you I misunderstood. :-)
Thanks again,
Morgan
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……
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 :)
Kimberly, do you know how (if in any way) this awful behavior (sp_MS_MarkSystemObject… blah…) depends on the build number?
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?