Constructing the iteration path in TFS

I had a situation where I had to go directly into the database and determine the work items that fell into a certain category and where they showed up on my long range plan (which iteration). It turns out that the iteration path isn’t easily accessible directly in the database. There are a number of places you can look, but none are particularly satisfactory.

The various tables are TreeIDPairs, TreeNodes, xxTree and the views are TreePairs, TreePathParts, TreesAreUsed and TreesWereUsed. All of these views and tables have problems with them in one way or another. For example, the TreePairs view has columns called Name (all values are a ‘’) and AboveID (all values are ‘0’) – not very useful. The TreesAreUsed contains the full Area Path but no Iteration Path. The xxTree has both but I preferred to not rely on that table (anything with an ‘xx’ to start the table name tells me it either is, or may be, deprecated). So I wrote my own query which I thought might be helpful to others.

WITH FullIterationPath (ID, ParentID, Name, tlevel, hierarchy)
AS
(
    --Anchor definition
    SELECT    a.ID,
            a.ParentID,
            a.Name,
            0 as tlevel,
            a.Name as hierarchy
    FROM    TreeNodes a
    WHERE    ID = 439    --FEC Solution-Products Root
    UNION ALL
    --Recursive definition
    SELECT    a.ID,
            a.ParentID,
            a.Name,
            tlevel + 1 as tlevel,
            cast(hierarchy + '' + a.Name as nvarchar(255)) as hierarchy
    FROM    TreeNodes a
    JOIN    FullIterationPath b ON b.ID = a.ParentID
)

SELECT    ID,
        ParentID,
        Name,
        tlevel,
        hierarchy
FROM    FullIterationPath

 

You can also use this to construct the area path as well.