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 ,
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 ,
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.