text: ﻿CREATE VIEW dbo.FilegroupStatus
  AS
  WITH F AS (
      SELECT InstanceID,
             DatabaseID,
             data_space_id,
             Instance,
             InstanceDisplayName,
             InstanceGroupName,
             ConnectionID,
             name,
             file_name,
             filegroup_name,
             physical_name,
             FileSizeMB,
             FileUsedMB,
             FileFreeMB,
             FilePctFree,
             FilegroupSizeMB,
             FilegroupUsedMB,
             FilegroupFreeMB,
             FilegroupPctFree,
             FilegroupNumberOfFiles,
             is_read_only,
             is_db_read_only,
             is_in_standby,
             state,
             state_desc,
             ExcludedReason,
             FreeSpaceStatus,
             FreeSpaceWarningThreshold,
             FreeSpaceCriticalThreshold,
             FreeSpaceCheckType,
             ConfiguredLevel,
             FileSnapshotDate,
             FileSnapshotAge,
  	       FileSnapshotAgeStatus,
  	       FilegroupMaxSizeMB,
  	       FilegroupPctOfMaxSize,
  	       FilegroupUsedPctOfMaxSize,
  	       PctMaxSizeStatus,
  	       MaxSizeExcludedReason,
  	       FilegroupAutogrowFileCount,
  	       FilegroupAutogrowStatus,
             type,
             type_desc,
  	       ROW_NUMBER() OVER(PARTITION BY DatabaseID,data_space_id ORDER BY FileID) rnum
      FROM dbo.FileStatus
  ) 
  SELECT NULL AS FileID,
  	   F.InstanceID,
         F.DatabaseID,
         F.data_space_id,
         F.Instance,
         F.InstanceDisplayName,
         F.InstanceGroupName,
         F.ConnectionID,
         F.name,
         NULL AS file_name,
         F.filegroup_name,
         NULL AS physical_name,
         F.FilegroupSizeMB AS FileSizeMB,
         F.FilegroupUsedMB AS FileUsedMB,
         F.FilegroupFreeMB AS FileFreeMB,
         F.FilegroupPctFree AS FilePctFree,
         F.FilegroupSizeMB,
         F.FilegroupUsedMB,
         F.FilegroupFreeMB,
         F.FilegroupPctFree,
         F.FilegroupNumberOfFiles,
         F.is_read_only,
         F.is_db_read_only,
         F.is_in_standby,
         F.state,
         F.state_desc,
         F.ExcludedReason,
         F.FreeSpaceStatus,
         F.FreeSpaceWarningThreshold,
         F.FreeSpaceCriticalThreshold,
         F.FreeSpaceCheckType,
         F.ConfiguredLevel,
         F.FileSnapshotDate,
         F.FileSnapshotAge,
  	   F.FileSnapshotAgeStatus,
  	   F.FilegroupMaxSizeMB,
  	   F.FilegroupPctOfMaxSize,
  	   F.FilegroupUsedPctOfMaxSize,
  	   F.PctMaxSizeStatus,
  	   F.MaxSizeExcludedReason,
  	   F.FilegroupAutogrowFileCount,
  	   FilegroupAutogrowStatus,
         type,
         type_desc
  FROM F 
  WHERE rnum=1