Get all referencing columns to a referenced column

Question

Say you have a Table named Sales with a SalesOrderId. Then you have two tables SalesOrderHeader and SalesReport that reference SalesOrderId. Starting from the Sales Tables and SalesOrderId, is there any way to figure out those two table reference it using linq? I want to add a Debug.Assert to my code so that I can update that piece of code whenever there is another foreign key constraint added against a column.

Answer 1

On the database side, these might help:

SELECT *
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'

SELECT [name] as FKeyName,
  (SELECT o1.[name] from sys.objects o1 where
    o1.[object_id] = fk.parent_object_id) as ParentTableName,
  (SELECT o2.[name] from sys.objects o2 where
    o2.[object_id] = fk.referenced_object_id) as ReferencedTableName
FROM sys.foreign_keys fk

If you really need the column names, pop open sp_helpconstraint in the system stored procedures and extract the logic from there.

---

On the designer side, the dbml file is xml... look for Database.Table.Type.Association