Get Cache Usage per Table

-- use <database_name>;

with memusage_CTE
         as (select bd.database_id,
                 bd.file_id,
                 bd.page_id,
                 bd.page_type,
                 COALESCE(p1.object_id, p2.object_id) as object_id,
                 COALESCE(p1.index_id, p2.index_id) as index_id,
                 bd.row_count,
                 bd.free_space_in_bytes,
                 CONVERT(tinyint, bd.is_modified) as 'DirtyPage'
             from sys.dm_os_buffer_descriptors as bd
             join sys.allocation_units as au
                 on au.allocation_unit_id = bd.allocation_unit_id
             outer apply(select top (1)
                             p.object_id,
                             p.index_id
                         from sys.partitions as p
                         where p.hobt_id = au.container_id
                            and au.type in(1, 3)) as p1
             outer apply(select top (1) p.object_id, p.index_id
                         from sys.partitions as p
                         where p.partition_id = au.container_id
                            and au.type = 2) as p2
             where bd.database_id = DB_ID()
                and bd.page_type in('DATA_PAGE', 'INDEX_PAGE'))
     select top 20 DB_NAME(database_id) as 'Database',
         OBJECT_NAME(object_id, database_id) as 'Table Name',
         index_id,
         COUNT(*) as 'Pages in Cache',
         SUM(DirtyPage) as 'Dirty Pages'
     from memusage_CTE
     group by database_id,
         object_id,
         index_id
     order by COUNT(*) desc;