Time after time, we hear the recommendation, "index your foreign keys." This is why this script was written. Now, the column or columns being referenced must be either a primary key or a unique constraint/index. That leaves referencing column(s) to be indexed...

This script omits statistics and system objects. I want to take this opportunity to address a limitation of the script. The script assumes the order of columns in an appropriate index will be the same order as in the constraint definition, per this join condition:

foreign_key_columns.constraint_column_id = index_columns.key_ordinal

This is not always true! For composite indexes (or indices), we are free to manipulate the key column order. A few guiding principles are:

1. SARGs go leftmost

2. Most selective column goes lefmost

3. Equality columns go leftmost

For more detailed discussion of these points, I recommend these articles: "The Best Indexes for Joins," SQL Server Magazine, May 2002; and "The Big Cover-Up," SQL Server Magazine, September 2001 both by Kalen Delaney.

Thus, you may have a foreign key appropriately indexed -- giving you better performance than an index in FK-definition order -- but still reported by this script...

Is there a reader out there that can formulate another script to allow for different but appropriate order? I'm also looking for a non-cursor based solution to identify potential foreign keys...

Thanks,

Mike