sql server all temp table sizes

use tempdb

--1

select object_name(p.object_id),

    sum(au.total_pages)*8 as [space_in_kb],

    sum(au.total_pages)*8/1024 as [space_in_mb],

    sum(au.total_pages)*8/1024/1024 as [space_in_gb]

    from sys.partitions p

    join sys.allocation_units au on p.hobt_id = au.container_id

    group by p.object_id

    order by [space_in_kb]  desc

---2

SELECT sysobjects.[name] AS [TableName],

    SUM(sysindexes.reserved) * 8 AS [Size(KB)],

    SUM(sysindexes.dpages) * 8 AS [Data(KB)],

    (SUM(sysindexes.used) - SUM(sysindexes.dpages)) * 8 AS [Indexes(KB)],

    (SUM(sysindexes.reserved) - SUM(sysindexes.dpages)) * 8 AS [Unused(KB)]

FROM dbo.sysindexes AS sysindexes

    JOIN dbo.sysobjects AS sysobjects ON sysobjects.id = sysindexes.id

WHERE sysobjects.[type] = 'U'

GROUP BY sysobjects.[name]

ORDER BY [Size(KB)] DESC