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

• «Previous Post
• Next Post »

---