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.
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