A common requirement when managing the resources of a database server is to be able to monitor the space available of the disk drives. Although there is no obvious way to do this through T-SQL there is an undocumented extended procedure called xp_fixeddrives.
As always with the undocumented procedures, it is not recommended to use this procedure in a production environment as Microsoft reserve the right to change or remove this procedure as pat of hot fixes, cumulative updates or service packs.
Warning out of the way, this is how I tend to use this procedure:
DECLARE @tblFixedDrives TABLE ( DriveLetter nchar(1) NOT NULL , MBFree decimal NOT NULL ) INSERT INTO @tblFixedDrives EXEC xp_fixeddrives SELECT * FROM @tblFixedDrives
I use this procedure in many ad-hoc reporting procedures as it provides valuable information that in conjunction with the other data about the size of databases can be very useful.