Get Table Size in T-SQL

select
    s.Name as SchemaName,
    t.NAME as TableName,
    p.rows as RowCounts,
    SUM(a.total_pages) * 8 as TotalSpaceKB,
    SUM(a.used_pages) * 8 as UsedSpaceKB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 as UnusedSpaceKB
from
    sys.tables t
inner join
    sys.schemas s on s.schema_id = t.schema_id
inner join
    sys.indexes i on t.OBJECT_ID = i.object_id
inner join
    sys.partitions p on i.object_id = p.OBJECT_ID and i.index_id = p.index_id
inner join
    sys.allocation_units a on p.partition_id = a.container_id
where
    t.NAME not like 'dt%'
    and t.is_ms_shipped = 0
    and i.OBJECT_ID > 255
group by
    t.Name, s.Name, p.Rows
order by 4 desc;