Posted By

ashcharlton on 12/01/13


Tagged


Versions (?)

Table relationship query


 / Published in: SQL
 

A query that lists all the tables with relationships to other tables, showing what columns are linked.

  1. SELECT t.name AS FKTableName
  2. , fk.name AS NameOfForeignKey
  3. , pc.name AS FKColumn
  4. , rt.name AS ReferencedTable
  5. , c.name AS ReferencedColumn
  6. FROM sys.foreign_key_columns AS fkc
  7. INNER JOIN sys.foreign_keys AS fk ON fkc.constraint_object_id = fk.object_id
  8. INNER JOIN sys.TABLES AS t ON fkc.parent_object_id = t.object_id
  9. INNER JOIN sys.TABLES AS rt ON fkc.referenced_object_id = rt.object_id
  10. INNER JOIN sys.COLUMNS AS pc ON fkc.parent_object_id = pc.object_id
  11. AND fkc.parent_column_id = pc.column_id
  12. INNER JOIN sys.COLUMNS AS c ON fkc.referenced_object_id = c.object_id
  13. AND fkc.referenced_column_id = c.column_id

Report this snippet  

You need to login to post a comment.