Listing 4: Using FOR XML PATH with the STUFF Function to Concatenate the Names of Files in a File Group

SELECT 
  vfs.database_id,
  DBName = DB_NAME(vfs.database_id),
  DB_Reads = SUM(vfs.num_of_reads),
  DB_Writes = SUM(vfs.num_of_writes),
  DB_BytesRead = SUM(num_of_bytes_read),
  DB_IOStallReadMS = SUM(io_stall_read_ms),
  DB_BytesWritten = SUM(num_of_bytes_written),
  DB_IOStallWriteMS = SUM(io_stall_write_ms),
  DB_IOStall = SUM(io_stall),
  DB_BytesSize = SUM(size_on_disk_bytes),
  DB_Files = dt.file_list
FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs
JOIN (
  SELECT 
    vfs2.database_id,
    STUFF((
    SELECT ',' + mf3.name AS [text()]
    FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs3
    JOIN sys.master_files mf3 ON mf3.database_id = vfs3.database_id
      AND mf3.file_id = vfs3.file_id
    WHERE vfs2.database_id = vfs3.database_id
    FOR XML PATH('')
  ), 1, 1, '') AS file_list
  FROM sys.dm_io_virtual_file_stats(NULL, NULL) vfs2
  JOIN sys.master_files mf2 ON mf2.database_id = vfs2.database_id 
  GROUP BY vfs2.database_id, DB_NAME(vfs2.database_id)
) dt ON dt.database_id = vfs.database_id
GROUP BY vfs.database_id, DB_NAME(vfs.database_id), dt.file_list
ORDER BY DB_NAME(vfs.database_id)