SQL script to get database sizes, disk information, and database recovery modes

database_sizes_and_fileinfo.sql

with fs
as
(
    select database_id, type, size * 8.0 / 1024 size
    from sys.master_files
)
select 
    name,
    (select sum(size) from fs where type = 0 and fs.database_id = db.database_id) DataFileSizeMB,
    (select sum(size) from fs where type = 1 and fs.database_id = db.database_id) LogFileSizeMB
from sys.databases db
order by DataFileSizeMB desc
go

EXEC MASTER..xp_fixeddrives
GO

SELECT name AS [Database Name],
recovery_model_desc AS [Recovery Model] FROM sys.databases
GO