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
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.
If you have access to the SQL server that hosts TFS database the db you have to look is TFSWarehouse, then you can look for the tables Work Item,tbl_Changeset,tbl_Identity,tbl_Version etc. from where you can pull some information.
Thnks.
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/