Find Missing Foreign Key References

If you database column naming convention says all foreign key columns must end with 'ID' then this is the query for you to find missing foreign key reference. E.g. PRIZE_ID means Foreign Key reference to PRIZE table.

SELECT 
    colT.TABLE_NAME , 
    colT.COLUMN_NAME 
FROM 
    INFORMATION_SCHEMA.COLUMNS AS colT 
WHERE 
    COLUMN_NAME LIKE '%id'
    AND 
    (colT.TABLE_NAME + colT.COLUMN_NAME) NOT IN
     ( SELECT 
          KOM.TABLE_NAME + KOM.COLUMN_NAME 
      FROM 
          INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS KOM)