Howto query all files, their fill level and additional drive info with one query? I need a query that puts together the following results:


one row per database file across all databases
additional columns with information about the locigal drive the file is on 
information about the size and the usage of the file 


So far I have put together the following query:

    SELECT 
     GETDATE() as dt
     ,@@SERVERNAME as srv
     ,F.name 
     ,F.physical_name    
     ,Round(F.size * 8 / 1024, 2) as FileSizeMb
    , CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS INT)/128 as FileUsedMB
    ,(F.size/128 - CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS INT)/128) AS FileFreeMB
    ,Convert(decimal(18,2), (F.size/128 - CAST(FILEPROPERTY(F.name, 'SpaceUsed') AS INT)/128) / (F.size * 8 / 1024.1) * 100) as SpaceFreePerc 
     ,stat.size_on_disk_bytes / 1024 / 1024 SizeOnDiskMb
     ,drv.volume_mount_point, drv.logical_volume_name, drv.available_bytes, drv.total_bytes 
 FROM sys.master_files F 
 inner join sys.dm_io_virtual_file_stats(NULL, NULL) stat on F.database_id = stat.database_id AND F.file_id = stat.file_id 
 CROSS APPLY sys.dm_os_volume_stats(F.database_id, F.FILE_ID) drv


Now I face two problems here:


The FILEPROPERTY SpaceUsed is only available for the database in current context - so I need a solution to collect all Fileproperties from all dbs / files.
CROSS APPLY dm_os_volume_stats does not work for dbs with compatibility level 80, but I have some of those in place.


Is there a way to reach this goal with the given conditions? The solution does not have to be a one-statement query, since it will anyways be part of a stored procedure in the end.
To avoid CROSS APPLY the simplest way I can think of is to call sys.dm_os_volume_stats with explicit parameters for database_id and file_id. This means executing a single-row result for every db/file combo.

First, create a #temp table to hold results:

CREATE TABLE #x(dt datetime, srv nvarchar(520), logical_name sysname,
  physical_name sysname, FileSizeMb int, FileUsedMB int, FileFreeMB int,
  SpaceFreePerc decimal(18,2), SizeOnDiskMB int, volume_mount_point nvarchar(256), 
  logical_volume_name nvarchar(256), available_bytes bigint, total_bytes bigint);


Now, some variables and a cursor. The only tricky part here really is the @exec variable, which allows each iteration of the dynamic SQL to be executed in the right database context.

DECLARE @database_id int, @file_id int, @logical_name sysname, 
  @physical_name nvarchar(520), @size int, @sql nvarchar(max), @exec sysname;

DECLARE c CURSOR LOCAL FAST_FORWARD
  FOR SELECT database_id, [file_id], name, physical_name, size
  FROM sys.master_files;

SET @sql = N'SELECT 
     GETDATE() as dt ,@@SERVERNAME as srv, @logical_name, @physical_name    
     ,Round(@size * 8 / 1024, 2) as FileSizeMb
     , CAST(FILEPROPERTY(@logical_name, N''SpaceUsed'') AS INT)/128 as FileUsedMB
     ,(@size/128 - CAST(FILEPROPERTY(@logical_name, N''SpaceUsed'') AS INT)/128) AS FileFreeMB
     ,Convert(decimal(18,2), (@size/128 - CAST(FILEPROPERTY(@logical_name, N''SpaceUsed'') AS INT)/128) / (@size * 8 / 1024.1) * 100) as SpaceFreePerc
     ,stat.size_on_disk_bytes / 1024 / 1024 SizeOnDiskMb
     ,drv.volume_mount_point, drv.logical_volume_name, drv.available_bytes, drv.total_bytes 
 FROM sys.master_files F 
 inner join sys.dm_io_virtual_file_stats(NULL, NULL) stat on F.database_id = stat.database_id AND F.file_id = stat.file_id 
 CROSS JOIN sys.dm_os_volume_stats(@database_id, @file_id) drv
 WHERE F.database_id = @database_id AND F.file_id = @file_id;';

OPEN c;
FETCH NEXT FROM c INTO @database_id, @file_id, @logical_name, @physical_name, @size;

WHILE (@@FETCH_STATUS &lt;&gt; -1)
BEGIN
  SET @exec = DB_NAME(@database_id) + N'.sys.sp_executesql ';    
  INSERT #x EXEC @exec @sql, N'@database_id int, @file_id int, 
    @logical_name sysname, @physical_name nvarchar(520), @size int',
    @database_id, @file_id, @logical_name, @physical_name, @size;

  FETCH NEXT FROM c INTO @database_id, @file_id, @logical_name, @physical_name, @size;
END

SELECT * FROM #x;

CLOSE c; DEALLOCATE c;