Data, log and total size of each user database in MB, with recovery model, compatibility level and state. Uses the file type rather than the .mdf/.ldf name, so extra .ndf files are counted. Works on SQL Server 2016 to 2022 and later.

-- Data, log and total size per user database in MB (SQL Server 2016-2022)
SELECT @@SERVERNAME AS instance_name,
       d.name       AS database_name,
       CAST(SUM(CASE WHEN mf.type_desc = 'ROWS' THEN mf.size END) * 8 / 1024.0 AS decimal(18, 0)) AS data_size_mb,
       CAST(SUM(CASE WHEN mf.type_desc = 'LOG'  THEN mf.size END) * 8 / 1024.0 AS decimal(18, 0)) AS log_size_mb,
       CAST(SUM(mf.size) * 8 / 1024.0 AS decimal(18, 0))                                       AS total_size_mb,
       d.recovery_model_desc,
       d.compatibility_level,
       d.state_desc
FROM sys.master_files AS mf
INNER JOIN sys.databases AS d ON d.database_id = mf.database_id
WHERE d.database_id > 4 -- skip system databases
GROUP BY d.name, d.recovery_model_desc, d.compatibility_level, d.state_desc
ORDER BY total_size_mb DESC;