THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Sp_indexinfo updated

For a long while, I've had a few things I wanted to add for sp_indexinfo (my procedure which returns bunch of information for indexes).

Dejan Sarka suggested adding XML indexes as well as making the column list look nicer. I've also had a request to add some documentation of what the procedure returns. Done. :-)

Published Wednesday, February 18, 2009 7:30 PM by TiborKaraszi
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Lidong said:

Hi Tibor,

do you want to add the filter definition to this sp?


February 19, 2009 1:28 AM

TiborKaraszi said:

Hmm, not sure what you mean by filer definition...

Do you mean as in filtered indexes? Hmm, that would mean two versions, one for 2005 and another for 2008. The addition is simple enough to do if you wish....

OK, here's what I do. I'll add it in there, but comment that line. So we still have only one version and if you wish to have this and you create the proc on 2008, you can just uncomment that line. The proc is up there in some 20 minutes. :-) Thanks for the tip!

February 19, 2009 2:46 AM

jamiet said:

Hi Tibor,

Don't s'pose you'd like to add that to SQL Mesh ( by any chance would you?


February 20, 2009 6:24 AM

JackMcC said:

Hi Tibor,

Great script however I found a tiny bug - if you have LOB data in a table the row for the Clustered will return double the actual row count. When you query sys.allocation_units it will produce a sum for IN_ROW_DATA and LOB_DATA resulting doubling the row count for the table.



October 21, 2010 4:21 AM

TiborKaraszi said:

Ahh, yes. Thanks Jack. I did a similar mistake with sp_tableinfo. I will look at it and fix, but I'm swamped at the moment. Will take a week or two... :-)

October 23, 2010 6:11 AM

TiborKaraszi said:

@JackMcC: Bug now fixed. Thanks again!

November 9, 2010 3:26 AM

Tibor Karaszi said:

November 9, 2010 3:29 AM

Henrik Staun Poulsen said:

Hi Tibor,

I've changed key_cols and incl_cols like this, so that I can copy the result from SSMS into Excel without linefeeds.

Best regards,



' + kc.column_name + CASE kc.is_descending_key

                                            WHEN 0 THEN ''  

                                            ELSE ' DESC'  


              AS VARCHAR(MAX))

AS [text()]  

FROM key_columns AS kc WITH (NOLOCK)

WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 0 AND kc.key_ordinal<>0 -- AND kc.partition_ordinal=0

ORDER BY key_ordinal  


),  1, 2, ''), CHAR(13), ''), CHAR(10), '') AS key_columns

, REPLACE(REPLACE(CASE WHEN i.type =1 THEN '(All columns)' --'cl'


 FROM key_columns AS kc WITH (NOLOCK)

 WHERE i.OBJECT_ID = kc.OBJECT_ID AND i.index_id = kc.index_id AND kc.is_included_column = 1

 ORDER BY key_ordinal

 FOR XML PATH('')  ), 1, 2, ''),'') END, CHAR(13), ''), CHAR(10), '') AS included_columns

February 29, 2016 9:54 AM

Leave a Comment


This Blog


Privacy Statement