The following SQL Server stored procedure could be useful when you need to get all disk space info quickly.
ALTER procedure [dbo].[usp_get_disk_space_info]
( @freeDiskSpace varchar(4000) output, @totalDiskSpace varchar(4000) output )
as
begin
exec ( 'sp_configure ''xp_cmdshell'', 1' )
exec ( 'reconfigure')
declare @psinfo table ( data nvarchar(1000) )
declare @t table(Drive varchar(2), FreeSpaceGB decimal(17,1), SizeGB decimal(17,1))
insert into @psinfo
EXEC xp_cmdshell 'Powershell.exe "Get-WMIObject Win32_LogicalDisk -filter "DriveType=3"| Format-Table DeviceID, FreeSpace, Size"'
delete from @psinfo where data is null or data like '%DeviceID%' or data like '%---%'
update @psinfo set data = REPLACE(data, ' ', ',');
;With DriveSpace as (
select SUBSTRING(data,1,2) as [Drive],
replace((left((substring(data,(patindex('%[0-9]%',data)) , len(data))),CHARINDEX(',', (substring(data,(patindex('%[0-9]%',data)) , len(data))))-1)),',','')
as [FreeSpace] ,
replace(right((substring(data,(patindex('%[0-9]%',data)) , len(data))),PATINDEX('%,%', (substring(data,(patindex('%[0-9]%',data)) , len(data))))) ,',','')
as [Size]
from @psinfo
)
insert into @t
SELECT Drive,
convert(dec( 6,2),CONVERT(dec(17,1),FreeSpace)/(1024*1024*1024)) as FreeSpaceGB,
convert(dec( 6,2),CONVERT(dec(17,1), size)/(1024*1024*1024)) as SizeGB
FROM DriveSpace;
declare @FreeSpaceGB varchar(1000)
declare @SizeGB varchar(1000)
set @FreeSpaceGB = stuff((select ', ' + Drive + ' ' + cast(FreeSpaceGB as varchar) + 'GB' from @t for XML PATH('') ), 1, 2, '')
set @SizeGB = stuff((select ', ' + Drive + ' ' + cast(SizeGB as varchar) + 'GB' from @t for XML PATH('') ), 1, 2, '')
select @freeDiskSpace = @FreeSpaceGB
select @totalDiskSpace = @SizeGB
exec ( 'sp_configure ''xp_cmdshell'', 0' )
exec ( 'reconfigure')
end