O DMV sys.dm_os_buffer_descriptors tem duas colunas que serão relevantes aqui: database_id e allocation_unit_id. Com o allocation_unit_id, você pode passar por sys.partitions para sys.indexes e, finalmente, para sys.objects. Isso parece obter a contagem por objeto dentro do contexto do banco de dados atual. Adaptar conforme necessário
SELECT
s.name AS [schema_name]
, o.name AS [object_name]
, i.name AS [index_name]
, COUNT( * )
FROM sys.dm_os_buffer_descriptors AS buff
INNER JOIN sys.[allocation_units] AS au
ON buff.allocation_unit_id = au.allocation_unit_id
INNER JOIN sys.partitions part
ON ( au.type IN ( 1, 3 )
AND au.[container_id] = part.[hobt_id] )
OR ( au.type = 2
AND au.[container_id] = part.[partition_id] )
INNER JOIN sys.data_spaces ds
ON au.[data_space_id] = [ds].[data_space_id]
INNER JOIN sys.[indexes] AS i
ON part.[object_id] = i.[object_id]
AND part.[index_id] = i.[index_id]
INNER JOIN sys.[objects] AS o
ON [i].[object_id] = [o].[object_id]
INNER JOIN sys.schemas s
ON [o].[schema_id] = [s].[schema_id]
WHERE o.is_ms_shipped = 0
AND buff.database_id = DB_ID( )
GROUP BY s.name
, o.name
, i.name
ORDER BY s.name
, o.name
, i.name