query to find the column value in another table

You may try to use Powershell script to get the info:

declare @serverName varchar(500)
declare @sql varchar(400)
set @serverName = @@SERVERNAME
set @sql = 'powershell.exe -c "Get-WmiObject -ComputerName ' + QUOTENAME(@serverName,'''') + ' -Class Win32_Volume -Filter ''DriveType = 3'' | select name,capacity,freespace | foreach{$_.name+''|''+$_.capacity/1048576+''%''+$_.freespace/1048576+''*''}"'

CREATE TABLE #Result
(list varchar(255))
insert #Result
EXEC xp_cmdshell @sql

select rtrim(ltrim(SUBSTRING(list,1,CHARINDEX('|',list) -1))) as drivename
      ,round(cast(rtrim(ltrim(SUBSTRING(list,CHARINDEX('|',list)+1,
      (CHARINDEX('%',list) -1)-CHARINDEX('|',list)) )) as Float)/1024,0) as 'capacity(GB)'
      ,round(cast(rtrim(ltrim(SUBSTRING(list,CHARINDEX('%',list)+1,
      (CHARINDEX('*',list) -1)-CHARINDEX('%',list)) )) as Float) /1024 ,0)as 'freespace(GB)'
from #Result
where list like '[A-Z][:]%'
order by drivename

drop table #Result