Walking A Dependency Tree

[simon.blackwell]

SQL 2005 supports heirachical navigation within the same table similar to Oracle's tried and true CONNECT BY. In a future rev of EA it would be useful to take advantage of this for a drill-down traceability matrix or dependency traces "uncluttered" by the rest of the elements in a UML model. Inthe short term, this is certainly useful for reporting. Here is the basics of the code in SQL. I have stripped out the joins required to get meaningful info from related object tables or stuff to limit the type of dependencyies that are followed:

WITH
  dependency (s,e)
  AS (SELECT [Start_Object_ID] As S1,[End_Object_ID] AS E1
      FROM   [dbo].[t_connector]
      WHERE  [Start_Object_ID] NOT IN(SELECT [End_Object_ID] FROM .[dbo].[t_connector])
      UNION ALL
      SELECT [Start_Object_ID] As S1,[End_Object_ID] AS E1
      FROM   [dbo].[t_connector] C
             INNER JOIN dependency D
                   ON D.e = C.[Start_Object_ID])
SELECT * FROM dependency

Here is a link to a useful site on this type of query:

http://www.sqlservercentral.com/columnists/fBROUARD/recursivequeriesinsql1999andsqlserver2005.asp