tfstfs-2012

TFS 2012 Discussion Users


Where can I find in the TFS database the user that entered a discussion item as shown here? I've found where the discussion items are on the WorkItemLongTexts table, and I see where the user data is on the Constants table, but I see no table or view that ties the two together.

TFS screen grab


Solution

  • I finally figured out how the discussion/comments users and timestamps are stored in the TFS database. It's a dumb design. You have to UNION the WorkItemsWere and WorkItemsAre tables and JOIN to the WorkItemLongTexts. I explored the "Change Order" and "Rev" columns to make the JOIN, but made no sense of it. Rather, you have to JOIN by the DATETIMES. I wouldn't normally advocate joining like this, but it was the only way I could make it work. In the end I was able to extract all my data, complete my conversion and finally put TFS to bed.

    Here's the query if you're interested...

    WITH WorkItems AS (
        SELECT ID, PersonId, [Changed Date]
        FROM WorkItemsAre
        UNION
        SELECT ID, PersonId, [Changed Date] 
        FROM WorkItemsWere 
    ), Discussion AS (
        SELECT *
        FROM WorkItemLongTexts
        WHERE FldID = 54
    )
    SELECT w.ID, PersonId, NamePart, d.AddedDate, Words
    FROM Discussion d
    INNER JOIN WorkItems w
    ON d.ID = w.ID
    AND d.AddedDate = w.[Changed Date]
    LEFT OUTER JOIN Constants c
    ON w.PersonId = c.ConstID
    ORDER BY w.ID, d.AddedDate