Retrieves database files and their space sizes.

DB_FileAllocationCheck.sql

select
    name [FileName]
    ,cast(size*1.0/128 as decimal(18,2)) [FileSizeinMB]
    ,cast(fileproperty(name,'SpaceUsed')/128 as decimal(18,2)) [SpaceUsedMB]
    ,cast(size/128.0 - cast(fileproperty(name,'SpaceUsed') as int)/128.0 as decimal(18,2)) [FreeSpaceMB]
    ,cast(cast(((cast(size as decimal(18,5)) - cast(fileproperty(name,'SpaceUsed') as decimal(18,5))) / size)*100 as decimal(18,2)) as varchar(10))+'%' [VolumeFree]
    ,'MaximumSizeinMB' =
        case max_size
            when 0 then 'No growth is allowed.'
            when -1 then 'Autogrowth is on.'
            when 268435456 then 'Log file will grow to a maximum size of 2 TB.'
            else cast (max_size*1.0/128 as nvarchar(30))
        end,
    growth 'GrowthValue'
    ,growth*8/1024 'GrowthValueinMB'
    ,'GrowthIncrement' = 
        case
            when growth = 0 then 'File size is fixed and will not grow.'
            when growth > 0 and is_percent_growth = 0 then 'Growth value is in units of 8-KB pages.'
            else 'Growth value is a percentage.'
        end
from sys.database_files