TFS: Find Files Affected Between Changesets

Question

We are using Team Foundation Server 2012.

We had a guy submit 97 files (not counting the designer and resource files) in Changeset 13646 on 8/9/2016.

Ten (10) days later on 8/18/2016, he rolled these changes back in Changeset 13716.

During that time, other work items were submitted and completed.

Is there some way to query TFS to find the Filenames and WorkItems that were included in any Changeset between 13646 and 13716?

I would like this to be an SQL query that I can run in SQL Server Management Studio.

Answer 1

There is also a way to do this with a SQL query using the Relational data warehouse. You will need access to the tfs_warehouse database for this. There are a few dimension tables you must join to get what you need:

• dbo.WorkItem
• dbo.WorkItemChangeset
• dbo.ChangeSet
• dbo.CodeChurn
• dbo.File

You can use dbo.WorkItemChangeset for the link between changesets and work items and dbo.CodeChurn for the link between changesets and files.

The documentation on this is old and incomplete, but here are some links that explain the fact and dimension tables:

• Understanding the Data Warehouse Architecture
• relational schemas
• work items schema
• source control schema

Note that their is only one Tfs_Warehouse database so the information of all projects in all project collections is stored here. Therefore you cannot trust changeset or work items ID's to be unique. These values are unique within a single project collection so in the Tfs_Warehouse the combination of project collection and changeset or workitem ID is unique.

Unfortunately I do not have access to a Tfs_Warehouse database at the moment so I'm unable to provide you with the exact query. But this question has a similar query you could modify.

Answer 2

tf history command with /format:Detailed parameter cannot display related work items, which won't meet your requirement.

It's not suggest to query database directly. Instead, you can use tf changeset command to display full information, which will include changed items and related work items:

tf changeset changesetnumber /noprompt /collection:TeamProjectCollectionUrl

In your case, you need to run two commands like:

tf changeset 13646 /noprompt /collection:TeamProjectCollectionUrl
tf changeset 13716 /noprompt /collection:TeamProjectCollectionUrl

Check my example in following screenshot:

Answer 3

The below command will return the list of files modified, along with the check-in comments, and I think you should see the work items as well (but I havent tested that part) into a log file. Please update the collection url, branch name in the below command.

tf history /collection:"tfsserverurl/collection" $/TFSbranchName /noprompt /recursive /format:detailed /v:C13646~C13716 > changeset.log

Example:

Changeset: 13646 User: LastName, FirstName Date: Thursday, February 16, 2017 4:26:50 PM

Comment: Added values/code back missed from December branch code merge

Items: edit $/TFSBranchName/abc.cs

Check-in Notes: Code Reviewer: Performance Reviewer: Security Reviewer:

If you copy all the "Items" from the log file (changeset.log) you should get all the files that were modified in those changsesets.

Answer 4

Though this is for TFS 2013 I'm hopeful it works in 2012. This was run against the tfs_warehouse database

 SELECT DISTINCT df.[FileName]
    ,df.FilePath
    ,dcs.ChangesetID AS 'ChangeSetID'
    ,dwi.System_id AS 'WorkItemID'
    ,dwi.System_WorkItemType
    ,dwi.System_State
    ,dwi.System_CreatedDate
    ,dwi.System_ChangedDate

FROM DimFile df 
JOIN FactCodeChurn fcc ON df.FileSK = fcc.FilenameSK
JOIN FactWorkItemChangeset fwi ON fcc.ChangesetSK = fwi.ChangesetSK
JOIN DimWorkItem dwi ON fwi.WorkItemID = dwi.System_id
AND fwi.TeamProjectCollectionSK = dwi.TeamProjectCollectionSK
AND fwi.RemovedDateTime = CONVERT(DATETIME, N'9999', 126)
JOIN DimChangeset dcs ON dcs.ChangesetSK = fcc.ChangesetSK 

WHERE dcs.ChangesetID BETWEEN 13646 AND 13716

ORDER BY dcs.ChangesetID

The file path does also have the filename within it, but I thought I would include the filename as a separate field