How to find what foreign key references an index on table

Question

I have a non clustered index I would like to drop (it is a duplicate of the clustered index). However, it is being used by a foreign key constraint. I would like to be able to identify which constraint is using the index, so I can update it to use the primary key index.

When I try to drop it:

DROP INDEX [idx_duplicate] ON [dbo].[MyTable]

I get an error:

An explicit DROP INDEX is not allowed on index 'dbo.MyTable.idx_duplicate'. It is being used for FOREIGN KEY constraint enforcement.

I tried to find the culprit with the following query but no luck:

SELECT name
FROM sys.foreign_keys
WHERE OBJECT_NAME (referenced_object_id) = 'idx_duplicate'

Answer 1

Something like

Select
    f.name,
    object_name(f.parent_object_id)
From
    sys.foreign_keys f
        inner join
    sys.indexes i
        on f.referenced_object_id = i.object_id and
           f.key_index_id = i.index_id
Where
    i.name = 'idx_duplicate' and
    i.object_id = object_id('[dbo].[MyTable]')

Answer 2

This will tell you the tables, the foreign key and the columns involved:

select f.name
  , parentTable = o.name
  , parentColumn = c.name
  , foreignTable = ofr.name
  , foreignColumn = cfr.name
from sys.foreign_keys f
  inner join sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id
  inner join sys.objects o on fc.parent_object_id = o.object_id
  inner join sys.columns c on fc.parent_column_id = c.column_id
    and o.object_id = c.object_id
  inner join sys.objects ofr on fc.referenced_object_id = ofr.object_id
  inner join sys.columns cfr on fc.referenced_column_id = cfr.column_id
    and ofr.object_id = cfr.object_id
  inner join sys.indexes i on ofr.object_id = i.object_id
where i.name = 'MyIndex'

SQL Fiddle with demo.

Answer 3

I realize that this post is a couple of years old but I wouldn't drop any such index without digging into it a LOT deeper. The non-clustered index is NOT the same as the clustered index even if the key columns are identical. The Leaf Level of the NCI is MUCH more narrow than the Leaf Level of the CI and, therefor, contains many more rows of information per page than the CI. You may actually be causing a performance problem (especially for inserts on the other table) by dropping the NCI.