A colleague of mine was in the middle of altering a database schema given some optimizations he was applying to the existing system, when he started to run into a frequent annoyance that disallowed him the easy removal of some troublesome columns: index dependencies for a given column.

In this particular instance, the database grew quickly beyond initial planned capacity and it had been tuned multiple times over by various people in order to bring performance under control. This left all the database entities with many indexes spanning across the same columns, similar sort orders, included columns, etc. When my friend was reviewing the design from a fresh perspective, he wanted to make simple column changes for performance and to meet the new requirements.

So, I get an e-mail saying: "What is a super secret sql script I could use to drop all dependent indexes on a given field so I can drop the field?" It is quite easy to write (and it does not drop the indexes, just lists them as simply dropping can be dangerous — for example, some columns might be included columns and not part of the primary index requirement):


select ColumnName = c.name

        , TableName = o.name

        , IndexName = i.name

, i.type_desc

        , i.is_unique

        , i.is_primary_key

        , i.is_unique_constraint

from sys.index_columns as ic

inner join sys.indexes as i

on ic.index_id = i.index_id and ic.object_id = i.object_id

inner join sys.columns as c

on c.object_id =ic.object_id and c.column_id = ic.column_id and objectproperty(ic.object_id,‘IsMSShipped’) = 0

inner join sys.objects o on ic.object_id = o.object_id

order by c.name, i.name, o.name