Do Your Foreign Key Columns Have Indices?

When I do a database audit, I always check for the existence of foreign keys that enforce referential integrity. That is often a great sign on whether or not a data-centric developer has been involved in the project. Once foreign keys have been created, indices must manually be configured, for SQL Server does not create them automatically.

I wrote this script to tell you if you have indices on all of your foreign key columns:

SELECT o.[Name] AS TableName, c.[Name] AS ColumnName
FROM sysForeignKeys f
JOIN sysObjects o
ON f.fkeyID = o.[id]
JOIN sysColumns c
ON c.[ID] = f.fkeyID AND c.[colid] = f.fkey
LEFT OUTER JOIN sysIndexKeys i
ON f.[fkeyid] = i.[id] AND f.[fkey] = i.[colid] AND f.[keyno] = i.[keyno]
WHERE i.[id] IS NULL

Even if referential integrity is being enforced at the application layer (GASP!), you should still have indices created on those virtual foreign key columns. Unfortunately, that will have to be audited manually by the application developer, which pretty much means it will never happen.