Friday, January 29, 2010

Index Columns

I seem to always have to rediscover how this works, so I'm going to record it.

This is how to join sys.indexes, sys.index_columns, and sys.columns.

SELECT ...
FROM sys.indexes        AS i
JOIN sys.index_columns  AS ic
  
ON   i.[object_id] ic.[object_id]
   
AND i.index_id    ic.index_id
JOIN sys.columns        AS c
  
ON   ic.[object_id] c.[object_id]
   
AND ic.column_id   c.column_id