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
