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!

2 thoughts on “Find missing foreign/primary keys in SQL Server

  1. 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

  2. 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

Comments are closed.