Friday 14th February 2025

One of features I use most often in Visual Studio Source Control Explorer is “View History”. While basic in its use it is also quite handy when trying to remember when I made a specific change. The other day we needed to pull all source history so we could analyze all changes during a set period, and for business reasons we wanted it in an Excel spreadsheet. I said sure no problem assuming it would be an easy task, but when I checked I could not find any easy option to do this.

As the informal DBA I’m quite familiar with SQL Server and knowing the TFS backend is hosted on one of our SQL VMs, I decided to go straight to the TFS SQL Database and export. I figured I’d be able to find the table where this data is stored, but again found it not so straightforward. Turns out there multiple DBs with 100s of tables and sprocs in each. After skimming through the different DBs and schemas, I found that most of the the check in data is in database “Tfs_DefaultCollection”, and user data is in “TFS_Configuration” in the tbl_Identity table.

If you want to get a complete query of every check in with who committed the change and when, the most basic version of this query is:

SELECT *
FROM [Tfs_DefaultCollection].[dbo].[tbl_ChangeSet] cs
LEFT [Tfs_Configuration].[dbo].[tbl_Identity] I on cs.committerid=I.id and cs.PartitionId=I.PartitionId

For our purposes this was enough and gave us a complete history of what changes were made by who and when, and I then just exported the results to an Excel spreadsheet. I really wanted to join each change to the project it was committed to, but given my time constraints never got that working. Hopefully this simple query helps you export TFS changeset data to Excel, and if you have a more advanced query that links each change to the relevant project please share!

Leave a Reply

Your email address will not be published. Required fields are marked *

Back To Top