What you end up with is a list of all your foreign keys and their supporting indexes! If "Supporting Index" is NULL then you should create one if you like following best practices. Feel free to email me with questions or improvements! You can download the script HERE.

Locate Missing Indexes needed to Support Foreign Key Constraints

Why do we create foreign keys? The reasons vary but the end result is an enforced reference between two tables. For example, say I have two tables, one named Shipments and another named Addresses. My company requires me to keep an audit of orders shipped to customers. I would create a foreign key from Shipments that references Address. This way, if someone tried to delete an address associated with a shipment, they would get a foreign key constraint error and not be able to bork company record keeping and orphan a shipment without it's associated address.

Now, consider what happens under the covers. Let's go back to our Shipments and Addresses table and pretend it's cleanup time! Say we have a data retention policy and want to delete shipments and addresses over a year old. In order to delete from Addresses, ALL associated Shipments must be deleted first. In order to not do a table scan on the Shipments table, I would need an index on the column that links it back to Addresses. I won't go into cascading deletes in this post, but suffice it to say that when you associate two tables, you need an index on both of the joining columns to get an index seek. This is considered an industry best practice.

So, how do you identify missing indexes? I wrote a handy script that checks for them. During the process I learned a foreign key can reference either the primary key for a table or a unique column. I included logic in my script that considers a primary key a supporting index along side standard clustered and nonclustered indexes. The column headings are self explanatory and below is a screenshot of the code and the output. You can download the script HERE.