Get SQL server database size, location and volume info using DMVs

SELECT 
    DB_NAME(mf.database_id) AS DatabaseName,
    mf.name AS LogicalFileName,
    mf.physical_name AS PhysicalFileName,
    CAST(mf.size * 8.0 / 1024 AS DECIMAL(10,2)) AS FileSizeMB,
    CAST(FILEPROPERTY(mf.name, 'SpaceUsed') * 8.0 / 1024 AS DECIMAL(10,2)) AS SpaceUsedMB,
    ovs.volume_mount_point AS VolumeMountPoint,
    ovs.logical_volume_name AS LogicalVolumeName,
    ovs.file_system_type AS FileSystemType,
    CAST(ovs.total_bytes / 1073741824.0 AS DECIMAL(10,2)) AS VolumeTotalGB,
    CAST(ovs.available_bytes / 1073741824.0 AS DECIMAL(10,2)) AS VolumeAvailableGB,
    CAST(100 * (ovs.available_bytes * 1.0 / ovs.total_bytes) AS DECIMAL(5,2)) AS VolumeFreePercent
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.file_id) ovs
ORDER BY DatabaseName, LogicalFileName;