Is there a clean T-SQL query I can use to verify an index has the right columns?

Question

I am writing a DB upgrade script that will check to see if an index has the right two columns defined. If it doesn't, or if it only has one of them, then I will DROP it (is there a way to ALTER an index?) and then recreate it with both.

Answer 1

I don't have a database immediately on-hand to test this, but you should be able to see if a column exists in an index by using the following IF EXISTS statement.

I'm not sure whether you can alter an index on the fly.

IF EXISTS
(
   SELECT MyIndex.Name AS IndexName, 
       Columns.name AS ColumnName 
   FROM sys.indexes MyIndex
   INNER JOIN sys.index_columns IndexColumns 
      ON  MyIndex.index_id = IndexColumns.index_id
      AND MyIndex.object_id = IndexColumns.object_id 
   INNER JOIN sys.columns Columns
      ON  Columns.column_id = IndexColumns.column_id 
      AND IndexColumns.object_id = Columns.object_id 
   WHERE Columns.name = 'ColumnName'
   AND MyIndex.Name='IX_MyIndexName'
)

Answer 2

Thanks for your help, Ed. Here is the solution I wrote using yours as a start. It has been verified. Basically it has all of the correct joins.

IF EXISTS
(
    SELECT i.Name AS IndexName, c.Name AS ColumnName
    FROM sys.indexes i
     JOIN sys.index_columns ic
      ON i.object_id = ic.object_id AND i.index_id = ic.index_id
     JOIN sys.columns c
      ON ic.object_id = c.object_id AND c.column_id = ic.column_id
    WHERE c.Name = 'MyColumnName' AND i.Name='MyIndexName'
)