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.
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"
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.
The Script does not use parameters, unfortunately it's all hard coded.
To download the script click
here.
/* The script goes through the current database and looks for foreign key relationships that are missing an index. The reason why this must be done is not only that TSQL commands usually are done using these relations but also the constraints used when validating CRUD interactions with the database use them, not many DBA's look at the query plans of these and they can therefore go unnoticed */ select OBJECT_NAME(fc.parent_object_id) as fkTable , fk.name as FKColumn , object_name(fc.referenced_object_id) as pkTable , pk.name as pkColumn , f.name as Releationship_Name from sys.foreign_key_columns as fc join sys.columns as pk on pk.object_id = fc.referenced_object_id and pk.column_id= fc.referenced_column_id join sys.columns as fk on fk.object_id = fc.parent_object_id and fk.column_id= fc.parent_column_id join sys.foreign_keys as f on f.parent_object_id= fc.parent_object_id and f.referenced_object_id= fc.referenced_object_id where not exists (select * from sys.index_columns as i where i.column_id =fk.column_id and i.object_id= fk.object_id )
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.