Find missing foreign/primary keys in SQL Server
Last week I wrote a SQL query to estimate how many columns are missing from foreign or primary keys. This works because of our naming convention for database keys:
- We use a Code suffix for natural keys e.g. CountryCode = NZ
- We use an ID suffix for surrogate keys e.g. EmployeeID = 32491
This script looks for any columns that match this naming pattern, but aren’t part of a primary or foreign key relationship.
-- Find columns on tables with names like FooID or FooCode which should-- be part of primary or foreign keys, but aren't.SELECT t.name AS [Table], c.name AS [Column]FROM sys.tables t INNER JOIN sys.syscolumns c ON c.id = t.object_id -- Join on foreign key columns LEFT JOIN sys.foreign_key_columns fkc ON (fkc.parent_object_id = t.object_id AND c.colid = fkc.parent_column_id) OR (fkc.referenced_object_id = t.object_id AND c.colid = fkc.referenced_column_id) -- Join on primary key columns LEFT JOIN sys.indexes i ON i.object_id = t.object_id and i.is_primary_key = 1 LEFT JOIN sys.index_columns ic ON ic.object_id = t.object_id AND ic.index_id = i.index_id AND ic.column_id = c.colidWHERE t.is_ms_shipped = 0 AND (c.name LIKE '%ID' OR c.name LIKE '%Code') AND ( fkc.constraint_object_id IS NULL -- Not part of a foreign key AND ic.object_id IS NULL -- Not part of a primary key ) AND ( -- Ignore some tables t.name != 'sysdiagrams' AND t.name NOT LIKE '[_]%' -- temp tables AND t.name NOT LIKE '%temp%' AND t.name NOT LIKE '%Log%' -- log tables -- Ignore some columns AND c.name NOT IN ('GLCode', 'EID', 'AID') -- external keys )ORDER BY t.name, c.name Using this script, I found over 200 missing foreign keys in one production database!