Get disk space info of all drives

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