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.colid
WHERE
	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!

December 21, 2008

2 Comments

Paul Fox on February 6, 2009 at 6:00 pm.

Thats great,

I made a couple of changes so that the FK column has to be the first column in the index. And it actually outputs a script to create the missing indexes.

SELECT
‘create index IX_’ + t.name + ‘_’ + c.name + ‘ on ‘ + t.name + ‘(‘ + c.name + ‘)’
FROM
sys.tables t

INNER JOIN sys.syscolumns c ON
c.id = t.OBJECT_ID

– Join on foreign key columns
inner 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)
and fkc.constraint_object_id IS not NULL

– only get the FKs where the first column is not indexed
where not exists (
select 1
from sys.index_columns ic
where
ic.OBJECT_ID = t.OBJECT_ID
AND ic.column_id = c.colid
–first column only
and ic.key_ordinal = 1
)
ORDER BY
t.name,
c.name

Tim Abell on June 29, 2012 at 10:54 am.

created a gist and added a primary key specific version
https://gist.github.com/3016993


-- Find tables with no primary key
SELECT
t.name AS [Table]
FROM
sys.tables t

-- Join on primary key columns
LEFT JOIN sys.indexes i ON
i.object_id = t.object_id
and i.is_primary_key = 1
WHERE
t.is_ms_shipped = 0
AND
(
i.object_id IS NULL -- Not part of a primary key
)
AND
(
-- Ignore some tables
t.name != 'sysdiagrams'
)
ORDER BY
t.name

Leave a Reply