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