In SQL 2005 I use this....
SELECT
object_name, counter_name, cntr_value/1024 as 'MBs used'
from
sys.dm_os_performance_counters
where
object_name = 'SQLServer:Memory Manager'
and
(counter_name = 'Total Server Memory (KB)' or counter_name = 'Target Server Memory (KB)')