Show SQL Server Tables Sizes

-- Shows schema, table name, row count, and size in KB, MB, GB:
select s.name as SchemaName,
    t.name as TableName,
    sum(p.rows) as RowCounts,
    sum(a.total_pages) * 8 as TotalSpaceKB,
    cast(sum(a.total_pages) * 8 / 1024.0 as decimal(18, 2)) as TotalSpaceMB,
    cast(sum(a.total_pages) * 8 / 1024.0 / 1024.0 as decimal(18, 2)) as TotalSpaceGB
from sys.tables as t
inner join sys.schemas as s
    on t.schema_id = s.schema_id
inner join sys.indexes as i
    on t.object_id = i.object_id
inner join sys.partitions as p
    on i.object_id = p.object_id
        and i.index_id = p.index_id
inner join sys.allocation_units as a
    on p.partition_id = a.container_id
where t.is_ms_shipped = 0
group by
    s.name,
    t.name
order by TotalSpaceMB desc;

--
-- To show the sum of all tables for a particular schema:
--

declare @SchemaName nvarchar(128) = N'Staging'; -- Change to your schema

select @SchemaName as SchemaName,
    sum(p.rows) as TotalRowCount,
    sum(a.total_pages) * 8 as TotalSpaceKB,
    cast(sum(a.total_pages) * 8 / 1024.0 as decimal(18, 2)) as TotalSpaceMB,
    cast(sum(a.total_pages) * 8 / 1024.0 / 1024.0 as decimal(18, 2)) as TotalSpaceGB
from sys.tables as t
inner join sys.schemas as s
    on t.schema_id = s.schema_id
inner join sys.indexes as i
    on t.object_id = i.object_id
inner join sys.partitions as p
    on i.object_id = p.object_id
        and i.index_id = p.index_id
inner join sys.allocation_units as a
    on p.partition_id = a.container_id
where t.is_ms_shipped = 0
    and s.name = @SchemaName;