ODBC Query to return Entity Name, Table Name, Erwin ID , Attribute Name, Column name, Column definition, physical Datatype and is Primary Key or Foreign Key.
Solution:
Select
E.NAME as 'Entity_Full-English_Name',
Tran(E.PHYSICAL_NAME) as 'Table_Physical_Name',
Tran( E.ID@) as 'ERwin_Entity_ID',
Tran( A.NAME) AS 'Attribute_Full-English_Name',
Tran(A.PHYSICAL_NAME) AS 'Column_Physical_Name',
A.ID@ as 'ERwin_Column_ID',
A.DEFINITION as 'Column_Defn',
A. PHYSICAL_DATA_TYPE as 'Physical Data_Type',
CASE
WHEN TRAN(A.Type) = 'Primary Key' AND A.Parent_Attribute_Ref Is Null THEN 'PK'
WHEN TRAN(A.Type) = 'Non Primary Key' AND A.Parent_Attribute_Ref Is Not Null THEN 'FK'
WHEN TRAN(A.Type) = 'Primary Key' AND A.Parent_Attribute_Ref Is Not Null THEN 'PK/FK'
Else 'Non-Key'
END AS 'Is PK-FK',
ISNULL( A.IS_LOGICAL_ONLY, 'F') AS 'Logical_Only?',
ISNULL( A.IS_PHYSICAL_ONLY, 'F') AS 'Physical_Only?'
FROM
M0.ENTITY E
INNER JOIN M0.ATTRIBUTE A ON E.ID@ = A.OWNER@
INNER JOIN M0.USER_ATTACHED_OBJECTS_REF RE ON RE.VALUE@ = E.ID@
ORDER BY E.Name
Note: You can also refer to sample queries at C:\Program Files\erwin\Data Modeler r9\ODBC Reports\ODBC Sample Reports