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.