| ■No88174 (魔界の仮面弁士) に追記
> ファイルのパスは sys.database_files から得られるので、
SELECT
sdb.name AS [DB名]
, smf.file_id AS [FileID]
, smf.name AS [論理名]
, smf.physical_name AS [ファイルパス]
, CASE ( smf.type )
WHEN CAST(0 AS tinyint) THEN 'データ'
WHEN CAST(1 AS tinyint) THEN 'ログ'
END AS [種別]
, CAST(sdf.size AS bigint) * 8192 AS [サイズ(Bytes)]
, CAST(CAST(sdf.size AS decimal) * 8192 / 1024 AS decimal(12, 2)) AS [サイズ(KB)]
, CAST(CAST(sdf.size AS decimal) * 8192 / 1024 / 1024 AS decimal(9, 2)) AS [サイズ(MB)]
, CAST(CAST(sdf.size AS decimal) * 8192 / 1024 / 1024 / 1024 AS decimal(5, 2)) AS [サイズ(GB)]
, CASE ( smf.is_percent_growth )
WHEN CAST(0 AS bit) then CAST(smf.growth * 8192 / 1024 AS varchar) + 'KB'
WHEN CAST(1 AS bit) then cast(smf.growth AS varchar) + '%'
END AS [増分]
FROM
sys.master_files smf
LEFT JOIN sys.databases sdb
ON smf.database_id = sdb.database_id
LEFT JOIN sys.database_files sdf
ON smf.file_id = sdf.file_id
ORDER BY
sdb.name
, smf.file_id |