create PROCEDURE BD_TopFolderSizeReport (@folderPath VARCHAR(100))
AS
BEGIN
SET NOCOUNT ON

DECLARE @director VARCHAR(500)
DECLARE @line VARCHAR(500)
DECLARE @command VARCHAR(500)
DECLARE @cntr BIGINT
DECLARE @filesize BIGINT

CREATE TABLE #SubTreeDirs (
dir_no BIGINT identity(1, 1)
,dirPath VARCHAR(500)
)

CREATE TABLE #TempTB (textline VARCHAR(500))

CREATE TABLE #OutReport (
Directory VARCHAR(500)
,FileSizeMB BIGINT
)

SET @command = ‘dir “‘ + @folderPath + ‘”‘ + ‘ /S/O/B/A:D’

INSERT INTO #SubTreeDirs
EXEC xp_cmdshell @command

SET @cntr = (
SELECT count(*)
FROM #SubTreeDirs
)

WHILE @cntr <> 0
BEGIN
SET @director = (
SELECT dirPath
FROM #SubTreeDirs
WHERE dir_no = @cntr
)
SET @command = ‘dir “‘ + @director + ‘”‘

TRUNCATE table #tempTB
INSERT INTO #tempTB
EXEC master.dbo.xp_cmdshell @command

SELECT @line = ltrim(replace(substring(textline, charindex(‘)’, textline) + 1, len(textline)), ‘,’, ”))
FROM #tempTB
WHERE textline LIKE ‘%File(s)%bytes’

SET @filesize = Replace(@line, ‘ bytes’, ”)

INSERT INTO #OutReport (
directory
,FilesizeMB
)
VALUES (
@director
,@filesize / (1024 * 1024)
)

SET @cntr -= 1
END

DELETE
FROM #OutReport
WHERE Directory IS NULL

SELECT TOP 10 *
FROM #OutReport
ORDER BY FilesizeMB DESC

DROP TABLE #OutReport

DROP TABLE #TempTB

DROP TABLE #SubTreeDirs

SET NOCOUNT OFF
END