TFS 2012 Discussion Users

Question

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.

Answer 1

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