SQL: Include a new column in an index

Question

I have a nonclustered index over two rows in a table, and that includes a number of columns. The index is used by a view and the view has now been updated to include another column. I generally script database changes so that they test first if the change has already been applied, which obviously means e.g. a column doesn't get added multiple times accidentally if the script is re-run.

I want to add the new column to the index by dropping and recreating it (unless there is a way to include the extra column without recreating the index). But I would like to test first to see if the index contains the column. Is there a simple way to check?

Answer 1

I think this will provide a list of columns on all your indexes (providing table name, index name and column name). I believe I got the links between them correct, but please check manually (e.g., test it on a index without the column, then on the new version of the index with the column).

Then you can add where clauses/IF EXISTS/etc as desired.

SELECT  so.name AS [TableName],
        si.name AS [IndexName],
        sc.name AS [IndexColumnName]
FROM    sys.indexes AS si
INNER JOIN sys.objects AS so ON so.object_id = si.object_id AND is_ms_shipped = 0
INNER JOIN sys.index_columns AS sic ON si.object_id = sic.object_id AND si.index_id = sic.index_id
INNER JOIN sys.columns AS sc ON sic.object_id = sc.object_id AND sic.column_id = sc.column_id

Note that sys.index_columns has a field is_included_column (which is either 1 or 0) which you can use to determine if it's an 'included' part of the index if required.