Query to generate Relationship Names, Parent and Child Table Names and PK and FK Column Names

Description:

Is there an ODBC Query which can be run in erwin DM Query Tool to get Relationship names, Parent and Child Table Names along with Primary Key (PK) and Foreign Key (FK)?

Solution:

You can get this report in Report Designer under Relationship Class. However, if you must use ODBC Query Tool, then here is the query:

SELECT
  Rel.Name 'Relationship Name'  ,
  Tran(PEn.Physical_Name) 'Parent Table Name',
  Tran(CEn.Physical_Name) 'Child Table Name', 
  Tran(Pat.Physical_Name) 'Parent Column Name',
  Tran(CAt.Physical_Name) 'Child Column Name'
FROM
  Relationship Rel
      LEFT OUTER JOIN Entity PEn
            ON Rel.Parent_Entity_Ref = PEn.Id@
      LEFT OUTER JOIN Entity CEn
          ON Rel.Child_Entity_Ref = CEn.Id@
      LEFT OUTER JOIN Attribute CAt
          ON CAt.Parent_Relationship_Ref = Rel.id@
      LEFT OUTER JOIN Attribute PAt
          ON CAt.Parent_Attribute_Ref = PAt.Id@ ;