[解決済み] sys.database_filesからデータベース名を返します。

質問

データベース名を返す方法をご存知の方はいらっしゃいますか？私は、どのように結合するかがわからないようです。 sys.database_files から 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

解決方法は？

使用方法 sys.master_files 代わりに、これは database_id - このように

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;

編集する。
クエリにデータベース名を追加する。

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;