T-SQL to find Memory Used by Database and its Objects

When a data page is read from disk, the page is copied into the SQL Server buffer pool and cached for reuse. Each cached data page has one buffer descriptor. Buffer descriptors uniquely identify each data page that is currently cached in an instance of SQL Server and we use this for calculating the memory used by the Databases and its objects.

-- Memory Used by All Databases
SELECT 
 CAST (COUNT(1)*8/1024 AS decimal(8,2)) AS [MemoryUsedByAllDB's(MB)]
,CAST (COUNT(1)*8/1024/1024 AS decimal(8,2)) AS [MemoryUsedByAllDB's(GB)]
FROM sys.dm_os_buffer_descriptors
GO

-- Memory Used by Individual Databases
SELECT
 CASE WHEN database_id = 32767 
      THEN 'ResourceDB' 
      ELSE DB_NAME(database_id) 
 END AS [DatabaseName]
,COUNT(1) AS [CachedPages]
,CAST (COUNT(1)*8/1024 AS decimal(8,2)) AS [MemoryUsed(MB)]
,CAST(COUNT(1)*8/1024/1024 AS decimal(8,2)) AS [MemoryUsed(GB)]
,CASE WHEN is_modified = 1 
      THEN 'Dirty Page' 
      ELSE 'Clean Page' 
 END AS [PageState]
FROM
sys.dm_os_buffer_descriptors
GROUP BY database_id
	,is_modified
ORDER BY CachedPages DESC
	,DB_NAME(database_id)
GO

-- Memory Used by Objects within a Database. Change the <<database name>> to your desiged Database Name
USE <<database name>>
GO
SELECT
 CASE WHEN database_id = 32767 
      THEN 'ResourceDB' 
      ELSE DB_NAME(database_id) 
 END AS [DatabaseName],
 InnerData.ObjectName,
 InnerData.ObjectType,
 InnerData.IndexName,
 InnerData.IndexType
,COUNT(1) AS [CachedPages]
,CAST (COUNT(1)*8/1024 AS decimal(8,2)) AS [MemoryUsed(MB)]
,CAST(COUNT(1)*8/1024/1024 AS decimal(8,2)) AS [MemoryUsed(GB)]
,CASE WHEN is_modified = 1 
      THEN 'Dirty Page' 
      ELSE 'Clean Page' 
 END AS [PageState]
FROM
sys.dm_os_buffer_descriptors SBD INNER JOIN 
(
SELECT
SAU.allocation_unit_id, 
SO.name AS [ObjectName],
SO.type_desc AS [ObjectType],
SI.name AS [IndexName],
SI.type_desc AS [IndexType]
FROM sys.allocation_units SAU INNER JOIN sys.partitions SP
ON SAU.container_id = SP.partition_id INNER JOIN sys.objects SO
ON SP.object_id = SO.object_id INNER JOIN sys.indexes SI
ON SO.object_id = SI.object_id
WHERE SO.is_ms_shipped=0
) AS InnerData
ON SBD.allocation_unit_id = InnerData.allocation_unit_id
WHERE DB_NAME(SBD.database_id) = DB_NAME()
GROUP BY database_id
	,is_modified
	,InnerData.ObjectName
	,InnerData.ObjectType
	,InnerData.IndexName
	,InnerData.IndexType
ORDER BY CachedPages DESC
	,InnerData.ObjectName
GO