Posts Tagged ‘foreign key’

Finding missing foreign keys in Transact SQL (for MS SQL Server)

Tuesday, May 25th, 2010

When I put together a database, I generally use integer ID fields to reference one table from another and I usually have the referencing field ending in “ID”.  e.g if I have Customer and Order tables, Order will have a CustomerID field.

The problem is that in a big database, it is easy to forget to enforce the foreign key constraints for each and every one, so I thought it would be useful to have a query to find them.  After a bit of playing about, I have ended up with…


select table_name,COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS cols where COLUMN_NAME <>'ID' and COLUMN_NAME like '%ID'
and TABLE_NAME not in('dtproperties')
and (select COUNT(*) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE keys where keys.TABLE_NAME=cols.TABLE_NAME and keys.COLUMN_NAME=cols.COLUMN_NAME)=0
order by TABLE_NAME

You can add in extra tables to exclude in the “TABLE_NAME not in” clause. I’ve put dtproperties in there because that is the internal table that was showing up when I didn’t want it to.

I’ve tested this in SQL Server 2008, but I suspect that it should be good for anything from SQL 7.0 upwards.