Cacher is the code snippet organizer for pro developers

declare @total_buffer int;
select @total_buffer = cntr_value
from sys.dm_os_performance_counters
where rtrim([object_name]) like '%Buffer Manager'
and counter_name = 'Total Pages';

;with src as (
    select database_id, db_buffer_pages = count_big(*)
    from sys.dm_os_buffer_descriptors
    --where database_id between 5 and 32766
    group by database_id
)
select  [db_name] = case [database_id]   when 32767 then 'Resource DB'
                                        else db_name([database_id])
                    end,
        db_buffer_pages,
        db_buffer_MB = db_buffer_pages / 128,
        db_buffer_percent = convert(decimal(6,3), db_buffer_pages * 100.0 / @total_buffer)
from src
order by db_buffer_MB desc;