Andy Brownsword goes searching:
That’s all well and good if we’re creating a new foreign key and adding our own index. How do we find existing foreign keys which aren’t yet indexed?
That’s where the query below comes into play.
We use a CTE to find foreign keys which are already indexed. From this we can then return details of other foreign keys along with scripts to create (and roll back) an index to mirror the key:
Click through for the script and notes on how it all works.