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 AS [Table], AS [Column]
	sys.tables t
	INNER JOIN sys.syscolumns c ON = 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.colid
	t.is_ms_shipped = 0
	AND ( LIKE '%ID' OR LIKE '%Code')
		fkc.constraint_object_id IS NULL -- Not part of a foreign key
		AND ic.object_id IS NULL -- Not part of a primary key
		-- Ignore some tables != 'sysdiagrams'
		AND NOT LIKE '[_]%' -- temp tables
		AND NOT LIKE '%temp%'
		AND NOT LIKE '%Log%' -- log tables
		-- Ignore some columns
		AND NOT IN ('GLCode', 'EID', 'AID') -- external keys

Using this script, I found over 200 missing foreign keys in one production database!