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

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

Answer 2

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.