SQL Server Indexes: Tibor Karaszi's sp_indexinfo

My virtual colleague and friend (long time between conference beers!), SQL Server MCT and MVP Tibor Karaszi has updated his excellent stored procedure, sp_indexinfo, which returns detailed information on SQL Server indexes.

His description, in part:

I can't count how many times I wanted to know "more" about physical attributes for a table, including index information. So I end up running sp_helpindex, some SELECT over sys.indexes, some reports in SSMS, some other queries against other catalog and dynamic management views, etc. I wrote this procedure for those cases. Here's my version of a procedure to give me information like:

• What indexes exists for a particular table or for each table in the database
• Clustered, non-clustered or heap
• Columns in the index
• Included columns in the index
• Unique or nonunique
• Number rows in the table
• Space usage
• How frequently the indexes has been used
• Any obvious indexes I should add?

For this, I use a number of sources, like sys.indexes, sys.columns, sys.partitions, sys.allocation_units, sys.data_spaces, sys.dm_db_index_usage_stats etc.

Go get the script here. And while you're on his site, check out his terrific explanation of the datetime data type.

Comments

[Tibor Karaszi]

Way too long, Chris. :-)