I thought this might be useful for some of you.

A question on StackOverflow asked if there’s a way to see any stats on git commits stored across a large number of repositories in TFS. The quick answer is “No, there isn’t. At least not directly”. The slightly longer answer is “Sure. If you’re willing to write a little SQL and maybe create a report to visualise the data”.

You may recall a little while back I posted on how TFS stores git repositories in its database. There are a number of tables in the database related to commits, and we can use this to get some useful information that we can then turn into stats. Here’s the query:

use [Tfs_DefaultCollection]

select r.Name, u.FullName, m.CommitTime, m.Comment 
from dbo.tbl_GitCommitMetadata m
left join dbo.tbl_GitCommit c on c.InternalCommitId = m.InternalCommitId
left join dbo.tbl_GitRepository r on r.InternalRepositoryId = c.InternalRepositoryId
left join dbo.tbl_GitCommitUser u on u.InternalId = m.CommitterId and u.PartitionId = m.PartitionId
order by r.Name, u.FullName, m.CommitTime

Here’s an example of the output from within SQL Management Studio, looking at a RestSharp repository I pushed to my TFS instance, so you can get an idea of the result:

image