0

Using either the Tfs_Warehouse.dbo.DimWorkItem (with System_WorkItemType = 'Code Review Request') and Tfs_Warehouse.dbo.DimChangeset tables, or the .CodeReview.tbl* and .dbo.tbl_ChangeSet tables, or some combination of those, what joins do I need in a SQL SELECT statement to relate a Changeset to the Code Review for that Changeset (assuming that one exists)?

I had this figured out for TFS 2012, but that query no longer works in 2015 due to the data model differences.

Zoe
  • 27,060
  • 21
  • 118
  • 148
Mark Freeman
  • 1,155
  • 3
  • 24
  • 42

1 Answers1

0

We don't recommend query database directly. Instead, we suggest you use TFS REST API to query information.

As we know, field Microsoft.VSTS.CodeReview.Context records the changeset number, so you can get a list of Code Review Request with field Microsoft.VSTS.CodeReview.Context. The request looks like:

Get http(s)://{instance}/DefaultCollection/_apis/wit/workitems?ids=xx,xx&fields=Microsoft.VSTS.CodeReview.Context&api-version=1.0

By the way, you can get IDs Code Review Request by running a query below:

POST http(s)://{instance}/DefaultCollection/_apis/wit/wiql?api-version=1.0
Content-Type: application/json
{
  "query": "Select [System.Id], [System.Title] From WorkItems Where [System.WorkItemType] = 'Code Review Request'"
}
Cece Dong - MSFT
  • 29,631
  • 1
  • 24
  • 39
  • I'm trying to build a SQL query to find all of the Changesets without a Code Review (along with the name of the Person who checked in the Changeset and the files in the Changeset). I shouldn't have to build a web application in 2015 to replace a single SQL statement in 2012. I wrote a stored procedure for 2012 that sends a daily email listing any such Changeset more than a few hours old to the department (excluding any files that are part of a later Changeset). I want to do the same thing for 2015. – Mark Freeman Aug 16 '16 at 18:10
  • I understand your requirement, but we don't recommend to operate database directly. We strongly suggest you use TFS API to get information. – Cece Dong - MSFT Aug 18 '16 at 03:19
  • At one call per Changeset, that sounds absurdly non-performant, not to mention an impractical development effort for a DBA trying to manage SQL Server schemas and TSQL code. – Mark Freeman Aug 18 '16 at 12:41