SQL Server: TSQL Script to Get Hard Drives Detail

Recently, I need a script which can provide me detail (Driver Letter, Drive Label, Free Space, Used Space etc) of all installed hard drives. I found following script by G. Rayburn very helpful.

Valid for versions: SQL Server 2005 and above

Expected Result:

 Note: Before executing script, don't forget to enable Ole Automation Procedures from Surface Area Configuration or using sp_configure.

/********************************************************

**    Author:  G. Rayburn

*********************************************************/

SET NOCOUNT ON

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveSpace')

      DROP TABLE ##_DriveSpace

IF EXISTS (SELECT name FROM tempdb..sysobjects WHERE name = '##_DriveInfo')

      DROP TABLE ##_DriveInfo

DECLARE @Result INT

      , @objFSO INT

      , @Drv INT

      , @cDrive VARCHAR(13)

      , @Size VARCHAR(50)

      , @Free VARCHAR(50)

      , @Label varchar(10)

CREATE TABLE ##_DriveSpace

      (

        DriveLetter CHAR(1) not null

      , FreeSpace VARCHAR(10) not null

       )

CREATE TABLE ##_DriveInfo

      (

      DriveLetter CHAR(1)

      , TotalSpace bigint

      , FreeSpace bigint

      , Label varchar(10)

      )

INSERT INTO ##_DriveSpace

      EXEC master.dbo.xp_fixeddrives

-- Iterate through drive letters.

DECLARE  curDriveLetters CURSOR

      FOR SELECT driveletter FROM ##_DriveSpace

DECLARE @DriveLetter char(1)

      OPEN curDriveLetters

FETCH NEXT FROM curDriveLetters INTO @DriveLetter

WHILE (@@fetch_status <> -1)

BEGIN

      IF (@@fetch_status <> -2)

      BEGIN

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

                  EXEC @Result = sp_OACreate 'Scripting.FileSystemObject', @objFSO OUTPUT

                        IF @Result = 0

                              EXEC @Result = sp_OAMethod @objFSO, @cDrive, @Drv OUTPUT

                        IF @Result = 0

                              EXEC @Result = sp_OAGetProperty @Drv,'TotalSize', @Size OUTPUT

                        IF @Result = 0

                              EXEC @Result = sp_OAGetProperty @Drv,'FreeSpace', @Free OUTPUT

                        IF @Result = 0

                              EXEC @Result = sp_OAGetProperty @Drv,'VolumeName', @Label OUTPUT

                        IF @Result <> 0

                              EXEC sp_OADestroy @Drv

                              EXEC sp_OADestroy @objFSO

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

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

                  INSERT INTO ##_DriveInfo

                        VALUES (@DriveLetter, @Size, @Free, @Label)

      END

      FETCH NEXT FROM curDriveLetters INTO @DriveLetter

END

CLOSE curDriveLetters

DEALLOCATE curDriveLetters

PRINT 'Drive information for server ' + @@SERVERNAME + '.'

PRINT ''

-- Produce report.

SELECT DriveLetter

      , Label

      , 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 ##_DriveInfo

ORDER BY [DriveLetter] ASC  

GO

DROP TABLE ##_DriveSpace

DROP TABLE ##_DriveInfo