Find foreign keys without indexes

Tere can be several reasons why one forgets to place indexes on foreign keys. Usually this is done in the design phase of the database when developers or DBA's use the graphical interface to drag and drop relationships between tables.

Having managed thousands of databases on hundreds of servers one has to come up with some quick, non intrusive analysis scripts to find out why the user on the other side of the phone is so upset and demands a "1024 Core server with 5 Terabytes of Ram"

What does this script do

The script goes over all table relations and looks for those table columns that have a relationship defined on them where the column not part of an index. The way this is done is by querying the SQL Server Management views and system tables.

  • Parameters
  • The Script
Parameters in the script

The Script does not use parameters, unfortunately it's all hard coded.
To download the script click here.

Important to know

The script might miss a relation if there is an index that is not used, one could query and look for disabled indexes used on foreign key relationships however I have never found there to be a need for it till now.