TSQL: Get drive space

Returns drive total size and free space in mb and percent free


CREATE PROCEDURE #prcDBA_Get_Drive_Space_Used
@pdrive char(1) ,
@pDriveSize numeric(20) OUTPUT
AS

DECLARE
@s varchar(13), --string holder
@i integer, --integer holder
@file_system_object integer,
@drive_letter integer,
@drive_size varchar(20)

SELECT
@s = 'GetDrive("' + @pdrive + '")'
EXEC
@i = sp_OACreate 'Scripting.FileSystemObject', @file_system_object OUTPUT
IF @i = 0
BEGIN
EXEC @i = sp_OAMethod @file_system_object, @s, @drive_letter OUTPUT
IF @i = 0
BEGIN
EXEC @i = sp_OAGetProperty @drive_letter,'TotalSize', @drive_size OUTPUT
IF @i <> 0
SELECT @drive_size = NULL
END
END

SELECT @pDriveSize = convert(numeric(20),@drive_size)

EXEC sp_OADestroy @drive_letter
EXEC sp_OADestroy @file_system_object
GO

SET NOCOUNT ON

--setup variables
DECLARE
@drive_letter char(1),
@mb_free integer,
@drive_size numeric(20)
DECLARE
@disk_space table
(
Drive char(1),
MBSize int, MBFree int
)

--get free space
CREATE TABLE #fixeddrives
(
Drive char(1),
MBFree integer NOT NULL
)
INSERT INTO
#fixeddrives
EXEC
master.dbo.xp_fixeddrives

--get total space
WHILE EXISTS (SELECT Drive FROM #fixeddrives WHERE Drive NOT IN (SELECT Drive FROM @disk_space))
BEGIN
SELECT @drive_letter = (SELECT TOP 1 Drive FROM #fixeddrives WHERE Drive NOT IN (SELECT Drive FROM @disk_space))
EXEC #prcDBA_Get_Drive_Space_Used @drive_letter, @drive_size OUTPUT
INSERT INTO @disk_space
(Drive, MBSize, MBFree)
SELECT
@drive_letter , CONVERT(integer,ROUND(@drive_size/1048576,0)) , MBFree
FROM #fixeddrives
WHERE Drive = @drive_letter
END
/*
DECLARE cur_drive_letter_cursor CURSOR FOR
SELECT
Drive,
MBFree
FROM
#fixeddrives
OPEN
cur_drive_letter_cursor
FETCH NEXT FROM cur_drive_letter_cursor INTO @drive_letter, @mb_free
WHILE @@FETCH_STATUS = 0

BEGIN
EXEC #prcDBA_Get_Drive_Space_Used @drive_letter, @drive_size OUTPUT
INSERT INTO @disk_space
(Drive, MBSize, MBFree)
VALUES
(@drive_letter, CONVERT(integer,ROUND(@drive_size/1048576,0)), @mb_free)
FETCH NEXT FROM cur_drive_letter_cursor into @drive_letter, @mb_free
END

CLOSE cur_drive_letter_cursor
DEALLOCATE cur_drive_letter_cursor
*/
--display drive, total, free and pct free
SELECT
Drive AS Drive,
MBSize AS MBSize,
MBFree AS MBFree,
MBFree*100/MBSize AS PercentFree
FROM
@disk_space
GO

--cleanup
DROP PROC #prcDBA_Get_Drive_Space_Used
DROP TABLE #fixeddrives