Get DBs files locations and drive details

SELECT DISTINCT 

dovs.database_id as database_id,

DB_NAME(dovs.database_id) as DBName,

mf.physical_name as PhysicalFileLocation,

dovs.logical_volume_name as LogicalDriveName,

dovs.volume_mount_point as Drive,

CONVERT(INT,dovs.available_bytes/1048576.0) AS FreeSpaceInMB

FROM sys.master_files mf

CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs

ORDER BY DB_NAME(dovs.database_id)