create PROCEDURE [dbo].[SP_DriversCheck2]
AS

SET NOCOUNT ON

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
eXEC sp_configure 'Ole Automation Procedures', 1;
RECONFIGURE;

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)
, @Volume_Label varchar(50)

CREATE TABLE ##_DriveSpace
(
DriveLetter CHAR(1) not null
, FreeSpace VARCHAR(10) not null

)

CREATE TABLE ##_DriveInfo
(
DriveLetter CHAR(1)
, TotalSpace bigint
, FreeSpace bigint
, Volume_Label varchar(50)
)

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', @Volume_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, @Volume_Label)

END
FETCH NEXT FROM curDriveLetters INTO @DriveLetter
END

CLOSE curDriveLetters
DEALLOCATE curDriveLetters

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

-- Produce report.
SELECT DriveLetter
, Volume_Label
, CAST(FreeSpace AS VARCHAR(15)) AS [FreeSpace MB]
, CAST( ROUND( CAST(FreeSpace AS FLOAT) / CAST(1024 AS FLOAT), 2) AS VARCHAR(15)) AS [FreeSpace GB]
, CAST((TotalSpace - FreeSpace) AS VARCHAR(15)) AS [UsedSpace MB]
, CAST( ROUND( CAST((TotalSpace - FreeSpace) AS FLOAT) / CAST(1024 AS FLOAT), 2) AS VARCHAR(15)) AS [UsedSpace GB]
, CAST(TotalSpace AS VARCHAR(15)) AS [TotalSpace MB]
, CAST( ROUND( CAST(TotalSpace AS FLOAT) / CAST(1024 AS FLOAT), 2) AS VARCHAR(15)) AS [TotalSpace GB]
, ((CONVERT(NUMERIC(9,0),FreeSpace) / CONVERT(NUMERIC(9,0),TotalSpace)) * 100) AS [Percentage Free]
INTO ##_DriveInfo2
FROM ##_DriveInfo
ORDER BY [DriveLetter] ASC

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ole Automation Procedures', 0;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
RECONFIGURE;

SELECT *
FROM ##_DriveInfo2
ORDER BY [DriveLetter] ASC

DROP TABLE ##_DriveInfo2