List of Files Changed in TFS

Recently, I was looking for a way to generate a list of all the files that have changed between a range of dates. My first thought was obviously to go look at the History in TFS. But I soon realized, as I'm sure you might guess, that this view is less than adequate in giving me a simple list of all changed files. I would have to drill into each change-set, and copy and paste the list of files into a spreadsheet or the like. Besides being mind numbingly tedious, I would still have to de-duplicate that list.

Doing a little searching on the web brought up various solutions, none of which really worked well for various reasons.

Granted, it is probably not a good idea to query TFS database directly, and the formating of the path is a little strange considering TFS replaces "_" with ">" for some reason. But under the circumstances, it was the only thing I could find that would give me the list that I needed.  Hopefully the version after 2010 will have better reporting for simple queries like this:


--USE [TfsVersionControl] -- TFS 2008
USE [Tfs_DefaultCollection] -- TFS 2010

declare @startDate datetime
declare @endDate datetime
declare @FileExclusion varchar(1000)
declare @filepath varchar(1000)

select @startDate = '10/29/2009', @endDate = '6/24/2010', @FileExclusion = '%VersionInfo.cs\',
@filepath = '$\MyProject\MyBranch\%'

SELECT distinct dfs.[FullPath], dfs.[MostRecentChange]
FROM
(SELECT v.[FullPath]
 , max(f.[CreationDate]) OVER (Partition by v.[FullPath]) AS [MostRecentChange]
 FROM [dbo].[tbl_Version] v
 LEFT JOIN [TfsVersionControl].[dbo].[tbl_File] f
  ON  f.[FileId] = v.[FileId]
  WHERE v.[FullPath] LIKE @filepath
  AND (NOT (v.[FullPath] LIKE @FileExclusion))
  AND f.[CreationDate] BETWEEN @startDate
    AND @endDate
  ) dfs
 --ORDER BY dfs.[MostRecentChange]
 ORDER BY dfs.[FullPath]