Why use the undocumented TfsVersionControl database when you're "encouraged" to use TfsWareHouse?
- The Transaction databases (TfsBuild, TfsVersionControl, TfsIntegration) are realtime, so you don't need to wait 30 minutes - 2 hours for it to refresh.
- 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...)
- 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).
V.VersionFrom, V.FullPath, L.CreationDate,
Replace(V.ChildItem, '\', '') as [FileName],
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)