Query TFS database to fetch last 10 check-in details

Question

Is there a way to query TFS database to get the last 10 check-in details

The output should be something like

File name    |      Comment              |   Changed By     |     Date
----------------------------------------------------------------------------
Test.cs         Added new functionality       username           01/08/2010

I am aware that the above result set can be obtained using TFS SDK. But I want to know if there is a way to query the TFS database to fetch the above data.

Thanks

Answer 1

If I understand your question correctly, this will get you most of the way there in SQL:

SELECT TOP 10
V.ChildItem AS [File name],
CS.Comment,
I.DisplayName AS [Changed By],
CS.CreationDate AS [Date]
FROM tbl_Changeset CS
INNER JOIN tbl_Identity I ON I.IdentityID = CS.OwnerID
INNER JOIN tbl_Version V ON V.VersionFrom = CS.ChangesetID
ORDER BY CS.CreationDate DESC

There are some escaped characters in the file names that showed up while I was testing this on my TFS instance (like underscore characters become ">"). Other than that this should serve you well.

Answer 2

A lot of these queries are no longer relevant with newer instances of TFS. One of the big reasons is that the user identities (tbl_Identity) have been moved. Tfs_DefaultCollection is the DB that stored all information pertinent to a given Collection, but TFS can host multiple Collections AND an Admin can change the name of the default Collection when they are setting up TFS.

As such, all user identities have been moved to the Tfs_Configuration database;

select * from [tfs_Configuration].dbo.tbl_Identity

Gaining Access to the descriptive part of the account name ( AccountName or DisplayName ) from a Collection DB is accomplished by Join from [tfs_DefaultCollection].dbo.tbl_IdentityMap

select *
from [tfs_Configuration].dbo.tbl_Identity I
JOIN [tfs_DefaultCollection].dbo.tbl_IdentityMap IM
    ON I.Id = IM.MasterID

With this information in hand, we can now produce the following query to show the last 100 ChangeSets committed to TFS as well as the name of the committer.

select top 100 *
from tbl_changeset as c
JOIN tbl_IdentityMap IM
    ON C.OwnerID = IM.localId
JOIN tfs_configuration.dbo.tbl_Identity u
    ON IM.MasterID = u.Id
Order by C.ChangeSetID DESC

Looking at this data, we can see A LOT of IDs, Sids, Lookups, etc. What we will NOT see in this data is any information about the File that was committed or information about the Branch the Commit was made to. This information comes from the tbl_Version table.

select top 100 *
from [tfs_DefaultCollection].dbo.tbl_changeset as c
JOIN [tfs_DefaultCollection].dbo.tbl_IdentityMap IM
    ON C.OwnerID = IM.localId
JOIN [tfs_configuration].dbo.tbl_Identity u
    ON IM.MasterID = u.Id
JOIN [tfs_DefaultCollection].dbo.tbl_Version as v
    ON v.Versionfrom = c.ChangeSetId
Order by C.ChangeSetID DESC

[tfs_DefaultCollection].dbo.tbl_Version has a few columns of interest, namely; ParentPath, ChildItem & FullPath. What is missing here is any useful information regarding the Branch that the commit was made to. Branch info is embedded in either of the 2 available Path fields;

$\da3da7cf"80b5"4385"b3dc"ebb3088f3c01\Features\Inpatient\Source\Yadda\Yadda\Yadda\

This begs the next question, where is Branch info stored in TFS? Thanks to StackOverflowUsers answer above, this information was found in [tfs_DefaultCollection].dbo.TreeNodes table;

select * from [tfs_DefaultCollection].dbo.[TreeNodes] where parentID=0 and fDeleted=0

The Column CssNodeID is the ID we are looking for to make sense out of the FullPath

CoolBranchName  da3da7cf-80b5-4385-b3dc-ebb3088f3c01

However, this presents us with our next challenge regarding the TFS Databases. A lot of information is encoded and\or embedded so we need to manipulate a few things to get what we are after.

In this instance, the part of the [tfs_DefaultCollection].dbo.tbl_Version.ParentPath or [tfs_DefaultCollection].dbo.tbl_Version.FullPath value that contains the Branch info.

This ugly little diddy right here extracts the ID portion of the ParentPath field value and replaces all the Double-Quotes with Hyphens which gives us an Id value we can use to query [Tfs_DefaultCollection].dbo.TreeNodes

SUBSTRING(
    REPLACE( v.ParentPath, '$\', ''),
    CHARINDEX( '\', REPLACE( v.ParentPath, '$\', '') ),
    ( LEN( v.ParentPath ) - CHARINDEX( '\', REPLACE( v.ParentPath, '$\', '') ) )
)

Putting all of this together in pursuit of a Query to Fetch The Last [X] Check-In details results in the following;

select top 10
    c.CreationDate,
    c.Comment,
    u.DisplayName as CommittingUser,
    TN.Name as BranchName,
    SUBSTRING(
        REPLACE( v.ParentPath, '$\', ''),
        CHARINDEX( '\', REPLACE( v.ParentPath, '$\', '') ),
        ( LEN( v.ParentPath ) - CHARINDEX( '\', REPLACE( v.ParentPath, '$\', '') ) )
    ) as ChangedFile
from tbl_changeset as c
JOIN tbl_IdentityMap IM
    ON C.OwnerID = IM.localId
JOIN [Tfs_Configuration].dbo.tbl_Identity u
    ON IM.MasterID = u.Id
JOIN dbo.tbl_Version as v
    ON v.Versionfrom = c.ChangeSetId
LEFT JOIN dbo.TreeNodes TN with(nolock)
    ON TN.CssNodeId = REPLACE(
        SUBSTRING(
            REPLACE( v.ParentPath, '$\', ''),
            0,
            CHARINDEX( '\', REPLACE( v.ParentPath, '$\', '') )
        ),
        '"', '-'
    )
    AND parentID=0
    AND fDeleted=0
Order by c.CreationDate desc

Note that I included the DB qualifiers for all the queries leading up to the final query to provide context on where tables of interest are located.

Answer 3

As a workaround how about the below query.. But i think it is returning me the wrong comments.. not sure why.

SELECT top 10
C.ChangeSetId, 
V.FullPath, 
V.ParentPath, 
REPLACE(V.ChildItem,'\','') as [FileName], 
C.CreationDate, 
I.DisplayName,
C.Comment
FROM tbl_Version(nolock) V
INNER JOIN tbl_File (nolock) F ON V.ItemId = F.ItemId
INNER JOIN tbl_Changeset (nolock) C ON V.VersionTo = C.ChangeSetId
INNER JOIN tbl_Identity (nolock) I ON C.CommitterId = I.IdentityId
where v.ParentPath like '$\' + (select name from [TfsWorkItemTracking].[dbo].[treenodes] where parentid=0 and fdeleted=0 and id=524) + '\%'
order by C.CreationDate desc

Thanks to mark.crockett for posting the above query @ http://social.msdn.microsoft.com/Forums/en-US/tfsreporting/thread/32d2c27e-825b-43bb-b156-36048a3e70cb/