Return Database Name from sys.database_files

Question

Does anyone know how the Database Name can be returned? I can't seem to figure out how to join sys.database_files to sys.databases.

SELECT
    a.NAME as FileName,
    FG.name as FileGroup,
    a.physical_name as FilePath,
    a.type_desc as FileType,
    CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)) AS [Size],
    CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)) AS [Used] ,
    CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) AS [Free]
FROM 
    sys.database_files a
LEFT JOIN sys.filegroups FG ON 
    FG.data_space_id = a.data_space_id

this seems helpfull:https://stackoverflow.com/questions/9630279/listing-information-about-all-database-files-in-sql-server — TheGameiswar, Sep 19 '17 at 06:17
`sys.database_files` returns files from the current database - not all databases. So `select db_name()` unless you are calling it with a three part name... — Martin Smith, Sep 19 '17 at 06:19

shA.t · Accepted Answer · 2019-04-06T04:30:38.383

Use sys.master_files instead - that has database_id - like this:

select 
    a.NAME as FileName,
    FG.name as FileGroup,
    a.physical_name as FilePath,
    a.type_desc as FileType,
    CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)) AS [Size],
    CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)) AS [Used] ,
    CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) AS [Free]
from sys.master_files a
LEFT JOIN sys.filegroups FG ON 
    FG.data_space_id = a.data_space_id;

Edit :
To add database name to the query:

select 
    d.name as DatabaseName,
    a.name as FileName,
    FG.name as FileGroup,
    a.physical_name as FilePath,
    a.type_desc as FileType,
    CONVERT(DECIMAL(12,2),ROUND(a.size/128.000,2)) AS [Size],
    CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(a.name,'SpaceUsed')/128.000,2)) AS [Used] ,
    CONVERT(DECIMAL(12,2),ROUND((a.size-FILEPROPERTY(a.name,'SpaceUsed'))/128.000,2)) AS [Free]
from sys.databases d
left join sys.master_files a on d.database_id = a.database_id
left join sys.filegroups FG on
    FG.data_space_id = a.data_space_id;

The user was asking how to get the database name. Your "solution" doesn't contain the database name... — David Wilson, Mar 29 '19 at 17:18
@DavidWilson Good point, thanks, I forgot to add a sample with database name ;). — shA.t, Apr 06 '19 at 04:31

score 0 · Answer 2 · answered Sep 19 '17 at 06:39

SELECT a.NAME as FileName ,b.name as FileGroup , b.physical_name as FilePath,   b.type_desc as FileType,
CONVERT(DECIMAL(12,2),ROUND(b.size/128.000,2)) AS [Size],
CONVERT(DECIMAL(12,2),ROUND(FILEPROPERTY(b.name,'SpaceUsed')/128.000,2)) AS [Used] ,
CONVERT(DECIMAL(12,2),ROUND((b.size-FILEPROPERTY(b.name,'SpaceUsed'))/128.000,2)) AS [Free]
FROM Sys.databases a 
INNER JOIN  Sys.master_files b ON a.database_id=b.database_id
WHERE a.database_id >4

Return Database Name from sys.database_files

2 Answers2