When working with TFS you often need to list files from the source control. This can be done in many ways, but as a geek, I want a SQL script to list and search for files. Off course, I can use the TFS Power Tool and the “Find in source control…” function.
But here is a script for listing and searching for files accross team projects. The below example searches for all files with filename containing ‘ADSync’.
SELECT
[FileName]
,SUBSTRING([FilePath], 1,
CHARINDEX('/', [FilePath], 3)) as TeamProject
,[FilePath]
,[LastUpdatedDateTime]
FROM [Tfs_Warehouse].[dbo].[DimFile]
WHERE [FileName] LIKE '%ADSync%'
AND [FileExtension] LIKE '%';
The below statement will count number of aspx files across all team project.
SELECT
SUBSTRING([FilePath], 1, CHARINDEX('/', [FilePath], 3)),
FileExtension,
COUNT(*)
FROM [Tfs_Warehouse].[dbo].[DimFile]
WHERE FileExtension in ('.aspx')
GROUP BY
SUBSTRING([FilePath], 1, CHARINDEX('/', [FilePath], 3)),
FileExtension
