Check Foreign Key Constraints

Checks a table and all the Foreign keys constraints on that table.

SELECT o1.name AS Referencing_Object_name
     , c1.name AS referencing_column_Name
     , o2.name AS Referenced_Object_name
     , c2.name AS Referenced_Column_Name
     , s.name AS Constraint_name

FROM sysforeignkeys fk
JOIN sysobjects o1 
  ON fk.fkeyid = o1.id
JOIN sysobjects o2 
  ON fk.rkeyid = o2.id
JOIN syscolumns c1 
  ON c1.id = o1.id AND c1.colid = fk.fkey
JOIN syscolumns c2 
  ON c2.id = o2.id AND c2.colid = fk.rkey
JOIN sysobjects s 
  ON fk.constid = s.id
 AND o2.name = 'table'