Database files information using query

database_files: Contains a row per file of a database as stored in the database itself. This is a per-database view

---database files into
select     f.type_desc as [Type]    ,f.name as [FileName]    ,fg.name as [FileGroup]
    ,f.physical_name as [Path]    ,f.size / 128.0 as [CurrentSizeMB]
    ,f.size / 128.0 - convert(int,fileproperty(f.name,'SpaceUsed')) /         128.0 as [FreeSpaceMb]
from 
    sys.database_files f with (nolock) left outer join 
        sys.filegroups fg with (nolock) on
            f.data_space_id = fg.data_space_id
option (recompile)

--System Procedures
sp_helpfile

Reference:

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-files-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-filegroups-transact-sql?view=sql-server-ver15

https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-master-files-transact-sql?view=sql-server-ver15