A query to find large TFVC files and the perpetrator

FindLargeVCFiles.sql

DECLARE @minSizeInMb INTEGER = 0;

SELECT ProjectName
	,SUBSTRING(_filePath, 1, LEN(_filePath) - 1) AS FilePath
	,ChangeSet
	,SizeInMB
	,CommittedBy
	,CommittedByEmail
	,CommitDate
FROM (
	SELECT ref.FileId
		,proj.ProjectName
		,CASE ver.VersionTo
			WHEN 2147483647
				THEN ver.VersionFrom
			ELSE ver.VersionTo
			END AS ChangeSet
		,REPLACE(REPLACE(REPLACE(ver.FullPath, '$\' + REPLACE(LOWER(cast(proj.ProjectSK AS VARCHAR(255))), '-', '"'), '$'), '"', '-'), '>', '_') AS _filePath
		,Round(cast(cast(meta.FileLength AS FLOAT) / 1024 / 1024 AS FLOAT), 2) AS SizeInMB
		,usr.UserDisplayName CommittedBy
		,usr.UserEmail CommittedByEmail
		,chn.CreationDate CommitDate
	FROM [dbo].[tbl_FileMetadata] meta
	LEFT JOIN dbo.tbl_FileReference ref ON ref.ResourceId = meta.ResourceId
	LEFT JOIN dbo.[tbl_Version] ver ON ver.FileId = ref.FileId
		AND ver.ItemType = 2
	LEFT JOIN AnalyticsModel.tbl_Project proj ON proj.ProjectSK = convert(UNIQUEIDENTIFIER, REPLACE(SUBSTRING(UPPER(ver.FullPath), 3, charIndex('\', ver.FullPath, 3) - 3), '"', '-'))
	LEFT JOIN [tbl_ChangeSet] chn ON chn.ChangeSetId = CASE ver.VersionTo
			WHEN 2147483647
				THEN ver.VersionFrom
			ELSE ver.VersionTo
			END
	LEFT JOIN [AnalyticsModel].[tbl_User] usr ON usr.[SpsUserId] = chn.CommitterId
	WHERE ver.FullPath IS NOT NULL
		AND Round(cast(cast(meta.FileLength AS FLOAT) / 1024 / 1024 AS FLOAT), 2) >= @minSizeInMb
	) AS Results
ORDER BY SizeInMB DESC