Find missing Foreign Keys in SQL Server
I was looking working on a script to find possibly missing foreign keys, when I stumbled upon Michael J Swart’s blog and it gave me exactly what I was looking for.
You might need to tweak this a little to match your naming conventions.
SELECT t.name, c.name FROM sys.columns c INNER JOIN sys.tables t ON t.object_id = c.object_id INNER JOIN sys.indexes i ON i.object_id = t.object_id LEFT JOIN sys.foreign_key_columns fkc_Parent ON fkc_Parent.parent_column_id = c.column_id AND fkc_Parent.parent_object_id = c.object_id LEFT JOIN sys.foreign_key_columns fkc_Referenced ON fkc_Referenced.Referenced_column_id = c.column_id AND fkc_Referenced.Referenced_object_id = c.object_id LEFT JOIN sys.index_columns ic ON ic.index_id = i.index_id AND ic.object_id = t.object_id AND ic.column_id = c.column_id WHERE fkc_Referenced.constraint_object_id IS NULL AND fkc_Parent.constraint_column_id IS NULL AND ic.index_column_id IS NULL AND c.name LIKE '%id' AND i.is_primary_key = 1 ORDER BY t.name, c.name
Here is another flavor I found on this blog
SELECT C.TABLE_SCHEMA,C.TABLE_NAME,C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS C INNER Join INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME And T.TABLE_TYPE = 'Base Table' AND T.TABLE_SCHEMA = C.TABLE_SCHEMA LEFT Join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U ON C.TABLE_NAME = U.TABLE_NAME And C.COLUMN_NAME = U.COLUMN_NAME And U.TABLE_SCHEMA = C.TABLE_SCHEMA WHERE U.COLUMN_NAME IS Null And C.COLUMN_NAME Like '%id' ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME
Here is mine with a small tweak. ?I added a Join to the?INFORMATION_SCHEMA.KEY_COLUMN_USAGE Table to ensure that the column is a Primary Key of some sort.
SELECT C.TABLE_SCHEMA,C.TABLE_NAME,C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS C JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME AND T.TABLE_TYPE = 'Base Table' AND T.TABLE_SCHEMA = C.TABLE_SCHEMA LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U ON C.TABLE_NAME = U.TABLE_NAME AND C.COLUMN_NAME = U.COLUMN_NAME AND U.TABLE_SCHEMA = C.TABLE_SCHEMA JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON C.COLUMN_NAME = K.COLUMN_NAME --Make sure that the column is a Primary Key WHERE U.COLUMN_NAME IS Null And C.COLUMN_NAME Like '%id' GROUP BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME
Here is my custom version that works for my naming conventions
DECLARE @TableMissingFK TABLE ( TABLE_SCHEMA nvarchar(128), TABLE_NAME sysname, TABLE_NAME_PK_COLUMN NVARCHAR(128), FK_TABLE_SCHEMA nvarchar(128), FK_TABLE_NAME sysname NULL, FK_COLUMN_NAME sysname NULL, FK_NAME VARCHAR(30)) INSERT @TableMissingFK (TABLE_SCHEMA, TABLE_NAME, FK_COLUMN_NAME) SELECT C.TABLE_SCHEMA,C.TABLE_NAME,C.COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS C JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME AND T.TABLE_TYPE = 'Base Table' AND T.TABLE_SCHEMA = C.TABLE_SCHEMA LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U ON C.TABLE_NAME = U.TABLE_NAME AND C.COLUMN_NAME = U.COLUMN_NAME AND U.TABLE_SCHEMA = C.TABLE_SCHEMA JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON C.COLUMN_NAME = K.COLUMN_NAME --Make sure that the column is a Primary Key WHERE U.COLUMN_NAME IS Null And C.COLUMN_NAME Like '%id' GROUP BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME UPDATE @TableMissingFK SET FK_NAME = SUBSTRING(FK_COLUMN_NAME, 1, CHARINDEX('_', FK_COLUMN_NAME)) UPDATE @TableMissingFK SET TABLE_NAME_PK_COLUMN = pkey.COLUMN_NAME, FK_NAME = FK_NAME + SUBSTRING(pkey.COLUMN_NAME, 1, CHARINDEX('_', pkey.COLUMN_NAME)) FROM @TableMissingFK ttbl --JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE keyu ON ttbl.TABLE_SCHEMA = keyu.TABLE_SCHEMA AND ttbl.TABLE_NAME = keyu.TABLE_NAME JOIN (SELECT tabc.TABLE_SCHEMA, tabc.TABLE_NAME, tabc.CONSTRAINT_NAME, cnst.COLUMN_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tabc JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cnst ON tabc.CONSTRAINT_NAME = cnst.CONSTRAINT_NAME WHERE tabc.CONSTRAINT_TYPE = 'PRIMARY KEY') pkey ON ttbl.TABLE_SCHEMA = pkey.TABLE_SCHEMA AND ttbl.TABLE_NAME = pkey.TABLE_NAME UPDATE @TableMissingFK SET FK_NAME = FK_NAME + 'FK' UPDATE @TableMissingFK SET FK_TABLE_SCHEMA = tabc.TABLE_SCHEMA, FK_TABLE_NAME = tabc.TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tabc JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cnst ON tabc.CONSTRAINT_NAME = cnst.CONSTRAINT_NAME JOIN @TableMissingFK ttbl ON cnst.COLUMN_NAME = ttbl.FK_COLUMN_NAME WHERE tabc.CONSTRAINT_TYPE = 'PRIMARY KEY' SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_NAME_PK_COLUMN, FK_TABLE_SCHEMA, FK_TABLE_NAME, FK_COLUMN_NAME, FK_NAME, 'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ADD CONSTRAINT [' + FK_NAME + '] FOREIGN KEY ([' + FK_COLUMN_NAME + ']) REFERENCES [' + FK_TABLE_SCHEMA + '].[' + FK_TABLE_NAME + '] ([' + FK_COLUMN_NAME + '])' FROM @TableMissingFK