0

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

SpeedRacer350
  • 55
  • 8
  • You should **never** write queries directly against a TFS operational store database. – Daniel Mann Jun 07 '21 at 19:09
  • @DanielMann That's exactly what I told my organization years ago, but here I am still. I'm a problem solver- not a cry baby, and your input was really not helpful. – SpeedRacer350 Jun 07 '21 at 21:47

1 Answers1

0

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
SpeedRacer350
  • 55
  • 8