(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: http://www.karaszi.com/SQLServer/util_sp_indexinfo.asp