SQL Server provides a built-in Stored Procedure to easily show the size of a database table, including the size of table indexes.

-- Syntax

sp_spaceused 'table_name';

-- Get All Table Sizes for the Current Database

create table #tables(
    TableName nvarchar(100),
    RowsCount int,
    KBReserved varchar(15),
    KBData varchar(15),
    KBIndex varchar(15),
    KBUnused varchar(15)

exec sp_msforeachtable
    @command1 = "print 'Running dbo.sp_spaceused on table ?'",
    @command2 = "insert into #tables exec dbo.sp_spaceused '?'";

select * from #tables order by TableName;
drop table #tables;