Query for Subject Area Name and Table and Column Properties

Description:

Using CA ERwin Data Modeler 8.2, Create a Query to report on....

Subject Area Name
 Table/ Name
 Table Comment
 Column Name
 Column Datatype
 Column Null Option
 Column Comment
 Column Identity Seed
 Column Is PK
 Column Is FK

Solution:

Please use the following....

SELECT
   SA.NAME                               'SA Name',
   Tran(PEn.Physical_Name)      'Table Name',
   Tran(PEn.Comment)            'Table Comment' ,
   Tran(PAt.Physical_Name)      'Column Name',
   PAt.Physical_Data_Type       'Datatype' ,
   TRAN(PAt.Null_Option_Type)   'Column Null Option',
   Tran(PAt.Comment)            'Column Comment',
   PAt.Is_Identity_Column       'Is Identity' ,
   PAt.Starting_Value           'Identity Starting Value' ,
   PAt.Increment_By             'Identity Increment By' ,
   'PK' = CASE
          WHEN TRAN (PAt.Type)  = 'Primary Key'
            THEN 'Yes'
             ELSE 'No'
          END,
   'FK' = CASE
          WHEN IFNULL (PRR.Physical_Name, 'No') = 'No'
           THEN 'No'
            ELSE 'Yes'
          END
FROM
   Entity PEn
      LEFT JOIN ER_MODEL_SHAPE EMS
        ON EMS.MODEL_OBJECT_REF = PEn.ID@
      LEFT JOIN ER_DIAGRAM ED
        ON ED.ID@ = EMS.OWNER@
      LEFT JOIN SUBJECT_AREA SA
        ON SA.ID@ = ED.OWNER@
      LEFT OUTER JOIN Attribute PAt
         ON PAt.Owner@ = PEn.Id@
      LEFT OUTER JOIN RELATIONSHIP PRR
        ON PAt.Parent_Relationship_Ref = PRR.Id@
Order By 1, 2,4