Get Index Included Column Info

Content rating: Beginner, tip

One of my pet peeves with SQL is that there isn't a quick and easy way to get all the information about indexes. Sure, you have sp_help tablename or sp_helpindex tablename to get the index name and keys for a table, but that's not even half the story. I use a lot of indexes with INCLUDE – more on that in the next blog – and INCLUDEd columns don't show up in sp_help and sp_helpindex.

To get around this, you can keep this query on hand, or just turn it into a view:

SELECT OBJECT_NAME(i.[object_id]) TableName ,

i.[name] IndexName ,

c.[name] ColumnName ,

ic.is_included_column ,

i.index_id ,

i.type_desc ,

i.is_unique ,

i.data_space_id ,

i.ignore_dup_key ,

i.is_primary_key ,

i.is_unique_constraint

FROM sys.indexes i

JOIN sys.index_columns ic ON ic.object_id = i.object_id

AND i.index_id = ic.index_id

JOIN sys.columns c ON ic.object_id = c.object_id

AND ic.column_id = c.column_id

ORDER BY tableName ,

ic.index_id ,

ic.index_column_id

That'll give you a lovely bit of information: the table, index, and column names, along with all your day to day index info needs. Slap a WHERE i.[name] = 'indexname' in there, and you get information for a particular index.

Jen McCown

http://www.MidnightDBA.com