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
Pingback: Tweets that mention SQL Awesomesauce » Blog Archive » Get Index Included Column Info -- Topsy.com
Pingback: Find Duplicate (and covered) Indexes | SQL Awesomesauce