TFS 2017 如何使用 SQL 查询工作项 [英] TFS 2017 How to query Work Items using SQL

问题描述

我正在寻找一种使用 SQL 选择直接从 TFS 的 SQL 数据库查询工作项的方法.

I'm searching for a way to query work items directly from the SQL database of TFS using SQL selects.

对于 TFS 2010，您可以使用一些数据库视图来实现此目标.TFS2010:如何在关系仓库上使用 SQL 查询工作项

For TFS 2010, there were some database views you could use to achieve this goal. TFS2010: How to query Work Items using SQL on the Relational Warehouse

WIQ 中的示例如下所示:

The example from the WIQ looks like this:

SELECT [System.Id], [System.WorkItemType], [System.Title], [System.AssignedTo], [System.State]
FROM WorkItems 
WHERE [System.TeamProject] = @project
  and [System.WorkItemType] = 'Ticket'
  and [System.State] <> 'Closed'
  and [System.State] <> 'Removed'

推荐答案

目前我发现的最好方法是使用数据库视图 vw_denorm_WorkItemCoreLatest.在这种情况下，最新"意味着您只能获得工作项的最新版本，而没有任何以前的版本.如果您需要工作项的所有版本，请使用视图 vw_WorkItemCoreAll.

The best way I've found so far is to use the database view vw_denorm_WorkItemCoreLatest. In this case "Latest" means, you only get the latest revision of the workitem without any previous versions of it. If you need all versions of a workitem, use the view vw_WorkItemCoreAll.

除了选定的列，这与我从我的问题中得到的 WIQ 一样接近:

Except the selected columns, this is as close as I got to the WIQ from my question:

SELECT *
FROM [dbo].[vw_denorm_WorkItemCoreLatest]
WHERE [System.TeamProject] = 'MyTeamProject'
  and [System.WorkItemType] = 'Ticket'
  and [System.State] <> 'Closed'
  and [System.State] <> 'Removed'

这篇关于TFS 2017 如何使用 SQL 查询工作项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！