C# と VB.NET の質問掲示板

■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