Modell-Suche in Package-Branch

The Enterprise Architect model repository is a database. Databases can be easily queried with SQL. This results in a wealth of possibilities. A minor problem is recursions! See this blog article.

The structure in the ProjectBrowser – the model tree – corresponds to a parentID, which is stored in the line of the child. To create a recursive query here, we need recursive SQL, which supports some databases.

Example of recursive SQL in MSSQL Server:

WITH MyCTE( Object_ID, Name) AS
  SELECT t_object.Object_ID, t_object.Name
  FROM t_object
  WHERE t_object.Name = 'HorstKargl'
  SELECT A.Object_ID, A.Name
  ON B.Object_ID = A.ParentID


Unfortunately, no recursive SQL can be called from an EA model search. Too bad. But there is a remedy!

The EA provides some keywords that will be replaced with a model search before it is executed:

  • #Author#
  • #Branch#:  Gets the IDs of the child Packages of the currently-selected Package, working recursively down to the lowest level of sub-Package. For example: t_object.Package_ID IN (#Branch#)
  • #Concat value1, value2 …#
  • #Datepart <field>, column#
  • #CurentElementGUID#
  • #CurrentElementID#
  • #DB=<DBNAME>#
  • #Now#
  • #Package#
  • #UserName#
  • #WC#. Gets the appropriate wild card for the current database, so the search can be performed on models on different databases. 

So we can:

  1. Create DB SQL syntax independent SQL.
  2. Especially with # Branch # search a special package branch.

Each model element stores the ID of the package in which it resides. The t_object.Package_ID is thus the parent in which the model element lies. If we use the keyword #Branch#, the PackageIds of the package currently selected in the Project Browser will be calculated and returned.

The SQL:

SELECT * FROM t_object WHERE t_object.Package_ID IN (#Branch#) AND t_object.Status = 'Proposed'

returns all model elements with the status 'Proposed', which are contained in the currently selected package in the Project Browser.

With the following query, we can populate a model chart in the EA with the restriction to only consider Model-Elements within a Package branch:

SELECT t_object.Status AS GroupName, t_object.Author AS Series FROM t_object WHERE t_object.Package_ID IN (#Branch#)

As a result, we get the following charts, depending on what is currently selected in the Project Browser. Even if no package is selected, the package_ID of the package in which the selected Model-Element or Diagram is currently located is used:


There are three permutations of the wild-card #Branch#:

  • #Branch#: gets the ID of each child Package of the parent Package selected by the user.
  • #Branch=<GUID># oder #Branch=<ID>#: gets the ID of each child Package of the parent Package specified by the GUID or ID.
    For example: #Branch={97C2EAE0-3C4D-4d0d-94DD-E7719B6119A2}#
  • #Branch=<ID>, <ID>,<ID> #: gets the ID of each child Package under each parent Package specified by its ID.


Posted in Model Search