Where is the story (product backlog item) description in TFS_Warehouse (TFS 2012)

Question

I am trying to build an SQL query for stories stored in TFS_warehouse (TFS 2012). What I want is a simple list of stories including basic elements such as id's, titles and the description. I am however unable to locate the description in the warehouse - can anybody tell me the name of the column and table where it is stored?

Answer 1

The following request will give you all the WorkItems with their last description and last comment :

select 
    WIL.ID,
    WAO.[System.WorkItemType],
    WIL.[Created Date],
    WAO.[System.CreatedBy],
    WAO.[System.AssignedTo],
    WIL.[Changed Date],
    WAO.[System.ChangedBy],
    WAO.[System.State],
    WAO.[System.Reason],
    WAO.[System.Title],
    (select top 1 left(REPLACE(REPLACE(replace(Words, ',', '\,'), CHAR(13), ' '), CHAR(10), ' '), 32000) from [dbo].[WorkItemLongTexts] where ID = WIL.ID and FldID = 52 order by AddedDate desc) [Description],
    (select top 1 left(REPLACE(REPLACE(replace(Words, ',', '\,'), CHAR(13), ' '), CHAR(10), ' '), 32000) from [dbo].[WorkItemLongTexts] where ID = WIL.ID and FldID = 54 order by AddedDate desc) [LastComment]
from 
    [dbo].[WorkItemsLatest] WIL
    inner join [dbo].[WorkItemsAsOf] WAO on WAO.[System.Id] = WIL.ID AND WAO.[System.Rev] = WIL.Rev
where 
    WAO.[System.TeamProject] = @ProjectName
order by
    WIL.[Created Date] ASC

You have to do it on the Tfs_XXXCollection database.

To check that your FieldId is the correct one, just do

select * from fields where [name] in ('Description','History')

You can easily get a full history if you want, but usually this would be enough to get you going.

Answer 2

The actual tables are in the TFS_[Collection] database in the WorkItemsAre and WorkItemsLatest tables.

Where [Collection] is the name of the collection.

All the TFS_[Collection] databases are rolled up into the TFS_Warehouse database.