Microsoft TFS Server Custom SQL Query

Microsoft's TFS 2017 has come a long ways, but is still a pretty terrible product.  That said, it is still the best option for many things as a Microsoft developer.

One thing I have seen a lot of people on the Internet asking about is various ways to get at the TFS information stored in SQL.  Microsoft obviously refuses to answer these questions just pointing people to their APIs.  However, sometimes it is just easier to run a quick SQL query.

For those people who are just wondering where Microsoft is hiding all the various fields, I have spent a good bit of time tracking some of them down.  Remember this code is for SQL 2017 and is unlikely to work for other versions, but it might point you in the right direction.  All this is is one of my queries with some comments, I will leave it up to you to go look at the tables and see how I use them to get the data.

The very last join I do is for a custom HTML field that I created in TFS, a very similar join on that table could be used to get other HTML fields as well, such as comments or description fields.

// Dashboards.tbl_Widget stores the Dashboard settings, you can hack changes here
// dbo.QueryItems has the settings for each query
// tbl_ChangeSet stores the change sets
// vw_WorkItemComments stores all the work item comments, including comments generated when changesets are submitted
// tbl_Version holds actual file references, can be linked to changesets using VersionFrom column
// WorkItemFiles are links between workitems and the changesets
// dbo.WorkItemLongTexts stores the long texts from bugs, like the main body
// dbo.tbl_TagDefinition contains the tag names themselves joins dbo.tbl_PropertyDefinition.Name on TagId
// dbo.tbl_Field contains the descriptions for the FieldId's from tbl_WorkItemCustomLatest
// dbo.tbl_PropertyDefinition string guid table join on dbo.tbl_PropertyValue on PropertyId integer value, ArtifactId is lookup key which is WorkItemId number converted to hex

select [System.Id] ID, l_title.[TextValue] [Title], l_approvedby.[StringValue] [ApprovedBy], l_valuearea.[StringValue] [ValueArea], l_priority.[IntValue] [Priority], l_waitingon.StringValue [WaitingOn]
-- Sub Select for Tags
,(select substring((select distinct ', ' + td.[Name]
from dbo.tbl_PropertyValue pv
Inner Join dbo.tbl_PropertyDefinition pd on pv.PropertyId = pd.PropertyId
Inner Join dbo.tbl_TagDefinition td on pd.[Name] = 'Microsoft.TeamFoundation.Tagging.TagDefinition.' + cast(td.TagId as varchar(100))
    Where pv.InternalKindId = 16 And pv.ArtifactId = CONVERT(VARBINARY(8), l.[System.Id])
for xml path('')), 3, 9000)) as Tags
,l_deploy.Words DeployNotes
from vw_denorm_WorkItemCoreLatest l
    inner join vw_denorm_WorkItemCustomLatest l_title on l.[System.Id] = l_title.Id And l_title.FieldId = 1
    left join vw_denorm_WorkItemCustomLatest l_approvedby on l.[System.Id] = l_approvedby.Id And l_approvedby.FieldId = 10133
left join vw_denorm_WorkItemCustomLatest l_valuearea on l.[System.Id] = l_valuearea.Id And l_valuearea.FieldId = 10055
    left join vw_denorm_WorkItemCustomLatest l_priority on l.[System.Id] = l_priority.Id And l_priority.FieldId = 10029
    left join vw_denorm_WorkItemCustomLatest l_waitingon on l.[System.Id] = l_waitingon.Id And l_waitingon.FieldId = 10134
    left join (select Max(AddedDate) AddedDate, Id from WorkItemLongTexts Where FldID = 11135 Group By Id ) l_deploy_grp on l.[System.Id] = l_deploy_grp.Id
left join WorkItemLongTexts l_deploy on l.[System.Id] = l_deploy.Id And l_deploy.FldID = 11135 And l_deploy_grp.AddedDate = l_deploy.AddedDate
Where
l.[System.IsDeleted] = 0
    And l.[System.State] = '20 Testing'
    And l.[System.AreaPath] = '\VerizonRAB'
And ( l.[System.WorkItemType] = 'Feature' Or l.[System.WorkItemType] = 'Bug' Or l.[System.WorkItemType] = 'Task' )