THE SQL Server Blog Spot on the Web

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

Tibor Karaszi

New version of sp_indexinfo

(See my initial blog post for general information about this proc.)

I just updated sp_indexinfo a bit:

  • I added the schema name as a new column in the output of the first resultset.
  • I added an optional second resultset with missing index information. This information is obviously drawn from the missing index dynamic management views. I'm not sure I generated the CREATE INDEX statment properly (the equality and inequality columns) since I didn't have much missing index entries to play with at the moment. All tests and replies are much welcome ("work fine" - "doesn't work" - "doesn't work because of..." - "change aaa to bbb" etc.).

I also updated the web site with some tips on creating a view in the databases where you want to work a lot so you can select from this view, aggregate etc. I will try to as much as possible stick with less procedural code and more set-based code. My aim is to have perhaps only two SELECT statements in the proc, so we can just take a SELECT statement, create a view or function inside your database and work with it from there. So, for instance, I will probably not add support for specify 'schemaname.tablename' for the first parameter as that will probably require some procedural parsing code (as seen in sp_helpindex). There's a trade-off between all the nice things you can complement using procedural code and having all in one or a few SELECT statments and being able to simply creating a view or table function from those.

One possible enhancement is to add fragmentation information. For this I will need to perform some type of join or subquery against sys.dm_db_index_physical_stats. My concern here is the cases where you don't want this information (because of cost for large tables/databases). Ideally I don't want two versions of the query (see above paragraph) but I also don't want to pay the penalty in cases where we don't want this info. Possibly this is doable using some correlated subquery as a column together with CASE, but this is only in my mind yet. Suggestions and thoughts are welcome.

You find the proc at: 

Published Tuesday, July 08, 2008 10:58 AM 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



jamiet said:

Excellent. Thank you Tibor!

July 8, 2008 4:38 AM

Alejandro Mesa said:

Hi Tibor,

Definitely something to keep in our tool belt.

I minor enhancement could be adding the sort order of the key columns, mainly the DESC ones.

Thanks for sharing it,


July 8, 2008 9:19 AM

AaronBertrand said:

+ ' INCLUDE(' + d.included_columns + ')'

Should be:

+ COALESCE(' INCLUDE(' + d.included_columns + ')', '')

Amazingly enough, the first time I tried this out, I actually found a case where included column(s) were NOT recommended.  :-)

July 8, 2008 2:49 PM

AaronBertrand said:

Also, I am getting suggestions to create indexes on objects that are clearly constraints... but I think this is just a coincidence on object_ids being common across multiple databases, as you are missing a filter on database_id -- since the DMVs hold information on objects in all databases.  In my case I only wanted to run the procedure in a single database, so adding this to the missing index query tidied up the results:

WHERE d.database_id = DB_ID()

July 8, 2008 2:56 PM

TiborKaraszi said:


Alejandro: Good suggestion. Added DESC for the descending columns. I didn't add ASC in order to limit "clutter".

Aaron: Great catches. Yeah, it seems a bit rare to not have included columns recommended - which is probably why I didn't see that in the first place.


July 9, 2008 4:03 AM

Leave a Comment

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement