Find storage including mount points Sql and windows utility

SET NOCOUNT ON;







DECLARE @v_cmd nvarchar(255)



        ,@v_drive char(99)



        ,@v_sql nvarchar(255)



        ,@i int



        



SELECT @v_cmd = 'fsutil volume diskfree %d%'



SET @i = 1







CREATE TABLE #drives(iddrive smallint ,drive char(99))



CREATE TABLE #t(drive char(99),shellCmd nvarchar(500));



CREATE TABLE #total(drive char(99),freespace decimal(9,2), totalspace decimal(9,2));







-- Use mountvol command to 



INSERT #drives (drive) 



EXEC master..xp_cmdshell 'mountvol'



DELETE #drives WHERE drive not like '%:\%' or drive is null







WHILE (@i <= (SELECT count(drive) FROM #drives))



BEGIN



     UPDATE #drives 



 SET iddrive=@i



 WHERE drive = (SELECT TOP 1 drive FROM #drives WHERE iddrive IS NULL)



 



 SELECT @v_sql = REPLACE(@v_cmd,'%d%',LTRIM(RTRIM(drive))) from #drives where iddrive=@i







 INSERT #t(shellCmd) 



 EXEC master..xp_cmdshell @v_sql



 



 UPDATE #t 



 SET #t.drive = d.drive



 FROM #drives d



 WHERE #t.drive IS NULL and iddrive=@i



 



            SET @i = @i + 1



END







INSERT INTO #total



SELECT bb.drive



        ,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(':',shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))



            ,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as freespace



        ,tt.titi as total



FROM #t bb



JOIN (SELECT drive



            ,CAST(CAST(REPLACE(REPLACE(SUBSTRING(shellCmd,CHARINDEX(':',shellCmd)+1,LEN(shellCmd)),SPACE(1),SPACE(0))



                ,char(13),SPACE(0)) AS NUMERIC(32,2))/1024/1024/1024 AS DECIMAL(9,2)) as titi



        FROM #t



        WHERE drive IS NOT NULL



                AND shellCmd NOT LIKE '%free bytes%') tt



    ON bb.drive = tt.drive



WHERE bb.drive IS NOT NULL



        AND bb.shellCmd NOT LIKE '%avail free bytes%'



        AND bb.shellCmd LIKE '%free bytes%';







-- SET FreespaceTimestamp = (GETDATE())



SELECT RTRIM(LTRIM(drive)) as drive



 ,freespace



 ,totalspace



 ,CAST((freespace/totalspace * 100) AS DECIMAL(5,2)) as [percent free]



FROM #total



WHERE (freespace/totalspace * 100) < 5



ORDER BY drive







DROP TABLE #drives



DROP TABLE #t



DROP TABLE #total