12

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.

4 Answers4

8

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

kevchadders
  • 8,335
  • 4
  • 42
  • 61
  • If I wanted to get better at doing this sort of thing, how do I go about it? Is there a site that offers a lot of TFS SQL information? The query works, but I wouldn't know how to build on it or modify it for use later. –  Mar 17 '17 at 21:28
  • Unfortunately, I have not come across a site dedicated to that sort of thing. I have picked it up myself by the odd snippet around the web e.g. https://www.visualstudio.com/en-gb/docs/report/sql-reports/work-item-link-history-tables and https://www.visualstudio.com/en-gb/docs/report/sql-reports/table-reference-relational-warehouse-database These links are for TFS 2013 upwards but hopefully they are mostly relevant to 2012 – kevchadders Mar 20 '17 at 10:25
  • you also have the existing reports you can use https://www.visualstudio.com/en-gb/docs/report/sql-reports/perspective-test-analyze-report-work and if your comfortable with SQL Profiler you can use that to pick out the tsql used – kevchadders Mar 20 '17 at 10:28
6

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:

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:

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.

Community
  • 1
  • 1
Sander Aernouts
  • 959
  • 4
  • 16
  • This looks like exactly what I've been trying to find. I'm exploring the query in the linked question you posted. There might be a way to modify it to return for me what I'm after. –  Mar 15 '17 at 13:52
  • Congratulations. –  Mar 21 '17 at 17:24
4

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:

enter image description here

Cece Dong - MSFT
  • 29,631
  • 1
  • 24
  • 39
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.

Isaiah4110
  • 9,855
  • 1
  • 40
  • 56
  • Is that run from the command line (not some TFS web interface console or SMS window)? –  Mar 10 '17 at 11:54
  • Run that from the VS developer command prompt. For example, VS 2013 it will be in the below location C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\Tools\Shortcuts\Developer Command Prompt for VS2013 – Isaiah4110 Mar 10 '17 at 15:26
  • Can the `/collection` parameter be a remote location? The url in our TFS browser is `w2000-dev:8080/tfs/CORE/Win/`. I can navigate to our **w2000-dev** PC on our network `\\w2000-dev\`, but the **tf** command keeps giving me "No URL can be found that corresponds to the following server name". –  Mar 10 '17 at 19:26
  • nope, thats your web portal url. Go to VS -> Team explorer pane -> you should see this in the home. Or click on the "connect" button (plug icon). If you are already connected you should get the server URL there. – Isaiah4110 Mar 10 '17 at 19:47
  • Were you able to get the TFS server url? Did the command work? – Isaiah4110 Mar 13 '17 at 13:43
  • No, I spent some time on it again this morning, but I wasn't able to find what you were talking about. –  Mar 13 '17 at 16:52
  • 1
    Open Visual Studio -> Team -> Manage Connections. Then on the team explorer pane -> Manage Connections -> Connect to team project. Now click on the server button and that should give yout the URL for your current repository. – Isaiah4110 Mar 13 '17 at 18:33
  • Well, that's different. This time I got a message saying I don't have access. It would be nice to make what I want into a stored procedure that I could keep on the server. –  Mar 13 '17 at 19:59
  • Thats strange, can you open the branch in visual studio -> source control explorer . Right click and check view history? This is a basic command and doesnt require any special rights to run. – Isaiah4110 Mar 13 '17 at 20:51
  • Yes, I can see all of that. I'm not sure what to put for a branch name up where you have `$/TFSbranchName`. Our TFS administrator does not let us do branches. I tried `Core/Win` and `DefaultCollection` (from [here](https://www.visualstudio.com/en-us/docs/tfvc/use-team-foundation-version-control-commands)), but I'm still doing something wrong. –  Mar 13 '17 at 21:13
  • TFSBranch is the folder name where you check-in your code in TFS. It takes the format $TFSprojectName/Foldername. – Isaiah4110 Mar 14 '17 at 13:56
  • No, I can't get that to work at all. So far, **kevchadders** answer works. I would mark his as the answer right now, but I really wished he would explain his answer some. –  Mar 17 '17 at 15:49
  • I can also save/export **kevchadders** answer to a CSV/Excel file to send in to management. –  Mar 17 '17 at 15:50