Query files in the TFS VersionControl database

TFS provides an API that C# could programmatically query source control. However, even with Linq, that may become tedious coding. TFS also provides a TfsVersionControl database that you can query directly with SQL. This has power.

Why use the undocumented TfsVersionControl database when you're "encouraged" to use TfsWareHouse?

1. The Transaction databases (TfsBuild, TfsVersionControl, TfsIntegration) are realtime, so you don't need to wait 30 minutes - 2 hours for it to refresh.
2. Not all the info is migrated to the TfsWareHouse (or at least, I can't find it in any documentation). For example, the warehouse has a File table, but it doesn't contain all versioned files (such as binaries, images, etc...)
3. The TFS warehouse may be corrupted (the process to sync it may be down)

Here's a simple (TFS 2008) query to get you started. It contains version, the full path, file name, and the CreateDate (when it was checked in). It's based on versioned items, so you can query history (you may also get duplicated, so you'd need to query that).

select

V.VersionFrom, V.FullPath, L.CreationDate,

Replace(V.ChildItem, '\', '') as [FileName],

V.*, L.*

from tbl_Version V (nolock)

inner join tbl_File L (nolock) on V.FileId = L.FileId

where V.ParentPath = '$\MyTeamProject\Folder\SubFolder\'

order by V.VersionFrom desc

Note that TFS by default stores paths in a different format, so you may need to convert:

·         '/' becomes \

·          '_' becomes >

·         '-' becomes " (double quote)