Drive Details using TSQL

SET NOCOUNT ON

DECLARE @res INT, 

@objdets INT,

@drive INT, 

@getdrive VARCHAR(13), 

@totalsize VARCHAR(50), 

@freespace VARCHAR(50), 

@DriveLetter char(1)

CREATE TABLE ##Drivespacedetails

(

DriveLetter CHAR(1),

TotalSpace bigint,

FreeSpace bigint,

)

INSERT INTO ##Drivespacedetails(DriveLetter,FreeSpace) EXEC master.dbo.xp_fixeddrives

DECLARE  drivedetails CURSOR FOR SELECT DriveLetter FROM ##Drivespacedetails

OPEN drivedetails

FETCH NEXT FROM drivedetails INTO @DriveLetter

WHILE (@@fetch_status <> -1)

BEGIN

SET @getdrive = 'GetDrive("' + @DriveLetter + '")' 

         EXEC @res = sp_OACreate 'Scripting.FileSystemObject', @objdets OUTPUT 

         IF @res = 0  

         EXEC @res = sp_OAMethod @objdets, @getdrive, @drive OUTPUT 

         IF @res = 0  

         EXEC @res = sp_OAGetProperty @drive,'TotalSize', @totalsize OUTPUT 

    IF @res = 0  

         EXEC @res = sp_OAGetProperty @drive,'FreeSpace', @freespace OUTPUT 

         IF @res <> 0  

         EXEC sp_OADestroy @drive 

EXEC sp_OADestroy @objdets 

SET @totalsize = (CONVERT(BIGINT,@totalsize) / 1048576 )

SET @freespace = (CONVERT(BIGINT,@freespace) / 1048576 )

     update ##Drivespacedetails set TotalSpace=@totalsize,FreeSpace=@freespace where DriveLetter=@DriveLetter

FETCH NEXT FROM drivedetails INTO @DriveLetter

END

CLOSE drivedetails

DEALLOCATE drivedetails

SELECT @@servername as ServerName,DriveLetter,FreeSpace AS [FreeSpace MB], (TotalSpace - FreeSpace) AS [UsedSpace MB], TotalSpace AS [TotalSpace MB], ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]

FROM ##Drivespacedetails ORDER BY [DriveLetter] ASC

DROP TABLE ##Drivespacedetails