I liked the code sample. I cleand it up by removing unused/unneeded variables, unneeded columns and tightend up on the error and loopin logic. Hope you approve of the updates.

set nocount on;

declare

@cmd nvarchar(130)

, @drive char(1)

, @freeMb int

, @hResult int

, @hWnd int

, @MB int

, @oDrive int

, @totalSize bigint

declare @drives table

( Drive char(1) not null primary key

, FreeMb int not null

, TotalMb int not null default (0)

);

-- initialize the variables

set @drive = '';

set @MB = 1048576; -- conversion ratio for bytes to megabytes

-- collect the attached Drives and FreeSpace

insert @drives(Drive, FreeMb) exec master.dbo.xp_fixeddrives ;

-- build the command to allow the creation/use of the FileSystemObject...

set @cmd = '

exec sp_configure ''show advanced options'', 1;

reconfigure;

exec sp_configure ''Ole Automation Procedures'', 1;

reconfigure;'

exec sp_executeSql @cmd;

-- create the FileSystemObject

exec @hResult = sp_OACreate 'Scripting.FileSystemObject', @hWnd out;

-- proceed if sucessful creating the OLE Object...

if @hResult = 0

begin

-- loop thru the drives and retrieve the total size...

while exists ( select top 1 * from @drives

where Drive > @drive )

begin

-- retrieve the next available drive...

select top 1

@drive = Drive

, @freeMb = FreeMb

from @drives

where Drive > @drive

order by Drive;

-- get the Drive object and then read the TotalSize (in bytes) property

exec @hResult = sp_OAMethod @hWnd, 'GetDrive', @oDrive out, @drive;

exec @hResult = sp_OAGetProperty @oDrive, 'TotalSize', @totalSize out;

-- update the drive...

update @drives

set TotalMb = @totalSize / @MB

where Drive = @drive;

end;

-- destroy the FileSystemObject object...

exec sp_OADestroy @hWnd;

-- return the results...

select Drive

, MbTotal

, MbFree

, convert(decimal(4,1), (MbFree / convert(float, MbTotal,0)) * 100, 0)) as PercentFree

from @drives

order by Drive;

end;

-- disable OLE Automation...

set @cmd = '

exec sp_configure ''Ole Automation Procedures'', 0;

reconfigure;

exec sp_configure ''show advanced options'', 0;

reconfigure;'

exec sp_executeSql @cmd;

return @@error;