如何通過一次查詢查詢所有文件、它們的填充級別和其他驅動器資訊？

我需要一個匯總以下結果的查詢：

• 所有數據庫中每個數據庫文件一行
• 附加列，其中包含有關文件所在的本地驅動器的資訊
• 有關文件大小和使用情況的資訊

到目前為止，我已經匯總了以下查詢：

   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

現在我在這裡面臨兩個問題：

1. FILEPROPERTY SpaceUsed 僅可用於目前上下文中的數據庫 - 所以我需要一個解決方案來收集所有 dbs / 文件中的所有 Fileproperties。
2. CROSS APPLY dm_os_volume_stats 不適用於兼容級別為 80 的 dbs，但我有一些。

有沒有辦法在給定的條件下達到這個目標？解決方案不必是單語句查詢，因為它最終還是儲存過程的一部分。

為了避免CROSS APPLY我能想到的最簡單的方法是sys.dm_os_volume_stats使用顯式參數呼叫database_idand file_id。這意味著為每個 db/file 組合執行單行結果。

首先，創建一個#temp 表來保存結果：

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);

現在，一些變數和一個游標。這裡唯一棘手的部分是@exec變數，它允許動態 SQL 的每次迭代都在正確的數據庫上下文中執行。

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 <> -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;

引用自：https://dba.stackexchange.com/questions/137489