Linking Pull Requests To The Workitems From The Tfs Database

We use custom queries (against TFS databases: Tfs_DefaultCollection and Tfs_Warehouse) to check if all changesets have a workitem linked to it. We use the FactWorkItemChangeset tab

Solution 1:

Cannot find the related table, however you can use the REST API to check if all the pull requests have a workitem linked to it. Please see Get Pull Requests By Project and Pull Request Work Items - List for details.

For example, below PowerShell script will retrieve all the Pull Requests from a specific project and list the linked work items for each of them, also output the Pull Requests which have no linked work items to a *.csv file ("D:\temp\1030.csv" in below sample).

Param(
   [string]$collectionurl = "http://172.17.16.163:8080/tfs/DefaultCollection", 
   [string]$project = "GitTest",
   [string]$user = "Domain\user",
   [string]$token = "password"
)

# Base64-encodes the Personal Access Token (PAT) appropriately$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user,$token)))

#Get pull request list for a specific project$prsurl = "$collectionurl/$project/_apis/git/pullrequests?api-version=2.0"$prs = (Invoke-RestMethod -Uri $prsurl -Method Get -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)})
$prurls = $prs.value.url

#Get the associated work items to PRs
Clear-Host

foreach ($prurl in $prurls)
{
$baseurl = "$prurl/workitems"$prwis = Invoke-RestMethod -Uri $baseurl -Method Get -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)}

write-host "Pull Reuquest:"$prurl

write-host "Count of Associated Work Items:"$prwis.count 
 foreach ($prwi in $prwis.value )
  {
   Write-Host "Associated workitem:"$prwi.id - $prwi.url
  }
  write-host `n

if ($prwis.count -lt 1)
 {
  #Write-host $prurl# Output the Pull Requests which have no work items associated:$prurl | Add-Content "D:\temp\1030.csv"  
 }
}

Solution 2:

We finally found the link in de Tfs_DefaultCollection.WorkItemFiles! For example: select * from [dbo].[WorkItemFiles] where FilePath = 'vstfs:///Git/PullRequestId/4f39e226-6f44-4e56-a216-f45969d8147d%2fab3368e0-56ef-468f-8e14-43065c433a21%2f2619'

This yields this result: ID 427787 FilePath vstfs:///Git/PullRequestId/4f39e226-6f44-4e56-a216-f45969d8147d%2fab3368e0-56ef-468f-8e14-43065c433a21%2f2619

The ID column contains the workitem id and the FilePath 2 hashes (separated by '%ef') and finally the pull request id. In this example workitem 427787 is linked to pull request 2619.