How to identify Foreign Key columns in a table via query on the model?

[Richard Freggi]

I reverse engineered a database and I'm writing the DML code to extract a data dictionary by querying the packages containing the tables, views etc.

I got everything working but I can't find what is the model database table/column for a table's Foreign Key.  PK and most everything else I got from qwerty's excellent "Inside EA" book, but naturally it does not cover Sparx MDGs so the Data Model profile is not covered.

t_connector has start/end object ID and FK as STEREOTYPE.  But I cannot map it into a specific column on the child table.

Any ideas?  Thanks!

[Geert Bellekens]

This is a part of the query I use to generate a document the mapping between the TDM (database model) and LDM

Code: [Select]

select tdm.* from
(
  select o.ea_guid AS CLASSGUID, o.Object_Type AS CLASSTYPE, o.name as Entity, null as Attribute, 'Table' as Type, tv.Value as LDMGuid, p1.Name as [Database],
  tvo.Value as SchemaName
  , CASE WHEN CHARINDEX('<description>',tv.NOTES) > 0 THEN
    SUBSTRING(tv.NOTES,CHARINDEX('<description>',tv.NOTES)+LEN('<description>'),
               CHARINDEX('</description>',tv.NOTES)-CHARINDEX('<description>',tv.NOTES) - LEN('<description>')) END as Comment
 , null as Datatype, null as PK, null as FK, null as NotNull, null as [Default] , null as FKTable, tv.Notes as MappingNotes
  from t_object o
  left join t_objectproperties tv on tv.Object_ID = o.Object_ID
                                                        and tv.Property in ('sourceElement','linkedAssociation', 'linkedAttribute')
  left join t_objectproperties tvo on tvo.Object_ID = o.Object_ID
and tvo.Property = 'Owner'
  inner join t_package p on p.Package_ID = o.Package_ID
  left join t_package p1 on p1.Package_ID = p.Parent_ID
  where 1 = 1
  and o.Object_Type = 'Class'
  and o.Stereotype = 'table'
  --and o.Package_ID in (" & sourcePackageTreeIDs  & ")
  union all
  select a.ea_guid AS CLASSGUID, 'Attribute' AS CLASSTYPE, o.name as Owner, a.Name as Attribute, 'Column' as Type, tv.VALUE as LDMGuid, p1.Name as [Database],
  tvo.Value as SchemaName
  , CASE WHEN CHARINDEX('<description>',tv.NOTES) > 0 THEN
    SUBSTRING(tv.NOTES,CHARINDEX('<description>',tv.NOTES)+LEN('<description>'),
               CHARINDEX('</description>',tv.NOTES)-CHARINDEX('<description>',tv.NOTES) - LEN('<description>')) END as Comment
 , a.type  + CASE WHEN dt.Size = 1 THEN '(' + cast(a.Length as varchar) + ')'
 WHEN dt.Size = 2 THEN '(' + cast(a.Precision as varchar) + ', ' + cast(a.Scale as varchar)  + ')'
  ELSE '' END as Datatype
 ,a.IsOrdered as PK, a.IsCollection as FK, a.AllowDuplicates as NotNull, a.[Default],fk.FKTable,tv.Notes as MappingNotes
  from t_attribute a
  inner join t_object o on o.Object_ID = a.Object_ID
  left join t_objectproperties tvo on tvo.Object_ID = o.Object_ID
and tvo.Property = 'Owner'
  left join t_datatypes dt on dt.ProductName = o.GenType
                                          and dt.DataType = a.Type
                                          and dt.Type = 'DDL'
  left join t_attributetag tv on tv.ElementID = a.ID
                                and tv.Property in ('sourceElement','linkedAssociation', 'linkedAttribute')
  left join (select opp.Name as kfName, op.Object_ID, o.Name as FKTable
                                                 from t_operationparams opp
                                                 inner join t_operation op on op.OperationID = opp.OperationID
                                                                               and op.Stereotype = 'FK'
                                                 inner join t_connector c on c.Start_Object_ID = op.Object_ID
                                                                               and c.StyleEx like '%SRC=' + op.Name + ':%'
                                                 inner join t_object o on o.Object_ID = c.End_Object_ID
                                     ) fk on fk.Object_ID = a.Object_ID
                                                 and fk.kfName = a.Name
                                                 and a.IsCollection = 1
  inner join t_package p on p.Package_ID = o.Package_ID
  left join t_package p1 on p1.Package_ID = p.Parent_ID
  where 1 = 1
  and o.Object_Type = 'Class'
  and o.Stereotype = 'table'
  --and o.Package_ID in (" & sourcePackageTreeIDs  & ")
  union all
  select c.ea_guid AS CLASSGUID, 'Association' AS CLASSTYPE, o.name as Owner,
  CONCAT_WS( '.', c.SourceRole, c.Name,c.destRole) as Attribute,
  'Association' as Type, tv.VALUE as LDMGuid, p1.Name as [Database],
  tvo.Value as SchemaName
  , CASE WHEN CHARINDEX('<description>',tv.NOTES) > 0 THEN
  SUBSTRING(tv.NOTES,CHARINDEX('<description>',tv.NOTES)+LEN('<description>'),
  CHARINDEX('</description>',tv.NOTES)-CHARINDEX('<description>',tv.NOTES) - LEN('<description>')) END as Comment
  , null as Datatype
  ,null as PK, 1 as FK, null as NotNull, null as [Default] ,fk.Name as FkTable ,tv.Notes as MappingNotes
  from t_connector c
  inner join t_object o on o.Object_ID = c.Start_Object_ID
and o.Object_Type = 'Class'
and o.Stereotype = 'table'
  left join t_objectproperties tvo on tvo.Object_ID = o.Object_ID
  and tvo.Property = 'Owner'
  left join t_connectorTag tv on tv.ElementID = c.Connector_ID
  and tv.Property in ('sourceElement','linkedAssociation', 'linkedAttribute')
  inner join t_object fk on fk.Object_ID = c.End_Object_ID
and fk.Stereotype = 'table'
  inner join t_package p on p.Package_ID = o.Package_ID
  left join t_package p1 on p1.Package_ID = p.Parent_ID
  where 1 = 1
  and c.Connector_Type = 'Association'
  and c.Stereotype = 'FK'
  --and o.Package_ID in (" & sourcePackageTreeIDs  & ")
  )tdm
Hope this helps

Geert

[Richard Freggi]

t_attribute.IsCollection it is!  Thanks very much!!!!