use master

go

if ((object_id('sp_osbufferdescriptors_agg') is not null)
    and (objectproperty(object_id('sp_osbufferdescriptors_agg'),'IsProcedure') = 1))
  drop proc [dbo].sp_osbufferdescriptors_agg

go

create proc [dbo].sp_osbufferdescriptors_agg
as
  /*
SAMPLE EXECUTION:
exec sp_osbufferdescriptors_agg
*/
  set nocount  on ;
  
  set transaction  isolation  level  read  uncommitted ;
  
  select   case 
             when grouping(dbName) = 1 then '--- TOTAL ---'
             else dbName
           end as dbName,
           case 
             when grouping(fileId) = 1 then '--- TOTAL ---'
             else fileId
           end as fileId,
           case 
             when grouping(pageType) = 1 then '--- TOTAL ---'
             else pageType
           end as pageType,
           count(* ) as countPages,
           sum(row_count) as sumRowCount,
           avg(row_count) as avgRowCount,
           sum(freeSpaceBytes) as sumFreeSpaceBytes,
           avg(freeSpaceBytes) as avgFreeSpaceBytes
  from     (select case 
                     when database_id = 32767 then 'resourceDb'
                     else cast(db_name(database_id) as varchar(25))
                   end as dbName,
                   cast(file_id as varchar(10)) as fileId,
                   cast(page_type as varchar(25)) as pageType,
                   row_count as row_count,
                   free_space_in_bytes as freeSpaceBytes
            from   sys.dm_os_buffer_descriptors bufferDescriptor with (nolock)) tmp
  group by dbName,fileId,pageType with rollup
  order by case 
             when grouping(dbName) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
             else dbName
           end,
           case 
             when grouping(fileId) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
             else fileId
           end,
           case 
             when grouping(pageType) = 1 then 'zzzzzzzzzzzzzzzzzzzzzzzzzzz'
             else pageType
           end;

go