Erwin Query Code for Generating Data Dictionary

We can query the Model using ERwin Data Modeler Query Tool.

The Erwin Query Code to generate the data dictionary is given below

SELECT

 TRAN(P_Attribute.Owner@)        "Entity Name",

TRAN(P_Entity.Definition)            "Entity Definition",

TRAN(P_Entity.owner_path)            "Model Name",

TRAN(P_Attribute.Name)                  "Attribute Name",

TRAN(P_Attribute.Definition)            "Attribute Definition",

TRAN(P_Entity.Physical_Name)         "Table Name",

TRAN(P_Attribute.Physical_Name)         "Column Name",

TRAN(P_Attribute.Physical_Data_Type)    "Column Data Type",

TRAN(P_Attribute.Null_Option_Type)      "Column Null Option",

CASE WHEN Keys.Key_Name            IS NULL THEN 'No' ELSE 'Yes' END "Column Is PK",

CASE WHEN P_Attribute.Parent_Attribute_Ref IS NULL THEN 'No' ELSE 'Yes' END "Column Is FK",

P_Attribute.attribute_order             "Logical Order",

P_Attribute.column_order                "Column Order",

P_Attribute.physical_order              "Physical Order",

'' "Column Description"

FROM    M0.Entity P_Entity

JOIN    M0.Attribute P_Attribute ON P_Attribute.owner@ = P_Entity.Id@

LEFT JOIN

(SELECT L_Entity.Name Entity_Name,

KEYGROUPMEMBER.Name Key_Name

FROM EM0.MV_Logical_Entity@ L_Entity

INNER JOIN EM0.MV_Logical_Key_Group@ LOGICALKEYGROUP ON L_Entity.Id@ = LOGICALKEYGROUP.Owner@

INNER JOIN M0.Key_Group_Member KEYGROUPMEMBER ON LOGICALKEYGROUP.Id@ = KEYGROUPMEMBER.Owner@

WHERE LOGICALKEYGROUP.Key_Group_Type = 'PK'

) Keys on Keys.Entity_Name = TRAN(P_Attribute.Owner@)and Keys.Key_Name = TRAN(P_Attribute.Name)

WHERE   P_Attribute.PARENT_ATTRIBUTE_REF is null

AND ISNULL(P_Entity.IS_LOGICAL_ONLY,'F') <> 'T'

AND ISNULL(P_ATTRIBUTE.IS_LOGICAL_ONLY,'F') <> 'T'

 UNION

SELECT 

TRAN(P_Attribute.CHILD_ATTRIBUTE_Owner@) "Entity Name",

TRAN(P_Attribute.CHILD_ENTITY_DEFINITION@)      "Entity Definition",

TRAN(P_Attribute.PARENT_ENTITY_owner_path@)     "Model Name",

TRAN(P_Attribute.CHILD_ATTRIBUTE_Name@)         "Attribute Name",

TRAN(P_Attribute.CHILD_ATTRIBUTE_Definition@)   "Attribute Definition",

TRAN(P_Attribute.Child_Entity_Physical_Name@)   "Table Name",

TRAN(P_Attribute.CHILD_ATTRIBUTE_Physical_Name@)         "Column Name",

TRAN(P_Attribute.CHILD_ATTRIBUTE_Physical_Data_Type@) "Column Data Type",

TRAN(P_Attribute.CHILD_ATTRIBUTE_Null_Option_Type@)      "Column Null Option",

CASE WHEN Keys.Key_Name IS NULL THEN 'No' ELSE 'Yes' END "Column Is PK",

CASE WHEN P_Attribute.CHILD_ATTRIBUTE_Parent_Attribute_Ref@ IS NULL THEN 'No' ELSE 'Yes' END "Column Is FK",

P_Attribute.CHILD_ATTRIBUTE_attribute_order@    "Logical Order",

P_Attribute.CHILD_ATTRIBUTE_column_order@       "Column Order",

P_Attribute.CHILD_ATTRIBUTE_physical_order@     "Physical Order",

'' "Column Description"

FROM    EM0.MV_FOREIGN_KEY_ATTRIBUTE@ P_Attribute

LEFT JOIN

(SELECT L_Entity.Name Entity_Name,

KEYGROUPMEMBER.Name Key_Name

FROM    EM0.MV_Logical_Entity@ L_Entity

JOIN    EM0.MV_Logical_Key_Group@ LOGICALKEYGROUP ON L_Entity.Id@ = LOGICALKEYGROUP.Owner@

JOIN    M0.Key_Group_Member KEYGROUPMEMBER ON LOGICALKEYGROUP.Id@ = KEYGROUPMEMBER.Owner@

WHERE   LOGICALKEYGROUP.Key_Group_Type = 'PK'

) Keys  ON  Keys.Entity_Name = TRAN(P_Attribute.CHILD_ATTRIBUTE_Owner@)

AND Keys.Key_Name = TRAN(P_Attribute.CHILD_ATTRIBUTE_Name@)

WHERE  (P_Attribute.CHILD_ATTRIBUTE_PHYSICAL_LEAD_ATTRIBUTE_REF@ IS NULL

OR      P_Attribute.CHILD_ATTRIBUTE_PHYSICAL_LEAD_ATTRIBUTE_REF@ = P_Attribute.CHILD_ATTRIBUTE_Id@ )

AND   ISNULL(P_Attribute.CHILD_ATTRIBUTE_IS_LOGICAL_ONLY@, 'F')  <> 'T'

AND     P_Attribute.CHILD_ENTITY_TYPE@ = '1075838979'