Get File and Maximimum Size by Drive Letter

create table #max(file_name varchar(30),
drive_name char(1),
max_size int,
file_size int)
go

exec master.dbo.sp_MSforeachdb "use [?] 

insert into #max
select convert(varchar(30),name)filename, substring(filename,1,1) drive,
'maxsize' = convert(int, maxsize * 8 / 1024),
'size' = convert(int, size * 8 / 1024)

from sysfiles"

select drive_name, sum(file_size) 'file size', sum(max_size) 'max_size'
from #max
group by drive_name
order by drive_name
go