Query to find size of each disk file in a location

set nocount on
CREATE TABLE #tempList (Files VARCHAR(500))

DECLARE @result int,@cmd sysname,@cmd3 sysname
DECLARE @var sysname = 'C:\BACKUP\MSSQLSERVER'

SET @cmd3 = 'DIR ' + @var

INSERT INTO #tempList
EXEC MASTER..XP_CMDSHELL @cmd3

--delete all directories
DELETE #tempList WHERE Files LIKE '%%'

--delete all informational messages
DELETE #tempList WHERE Files LIKE ' %'

--delete the null values
DELETE #tempList WHERE Files IS NULL

--get rid of dateinfo
UPDATE #tempList SET files =RIGHT(files,(LEN(files)-20))

--get rid of leading & Trailingspaces
UPDATE #tempList SET files = RTRIM(LTRIM(files))

--split data into size and filename
SELECT row_number() OVER(ORDER BY Files DESC) AS Row,
LEFT(files,PATINDEX('% %',files)) AS Size_Bytes,  --File size in bytes
RIGHT(files,LEN(files) -PATINDEX('% %',files)) AS FileName,
replace(LEFT(files,PATINDEX('% %',files)),',','') /1024 as FileSize_KB,  --File size in KB
replace(LEFT(files,PATINDEX('% %',files)),',','') /1024/1024 as FileSize_MB  --File size in MB
FROM #tempList
where LEFT(files,PATINDEX('% %',files)) like '%,%'


DRop table #tempList
set nocount off