Display All Tables With Sizes

select

    t.name as TableName, s.name as SchemaName, 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.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)

left outer join sys.schemas s on t.schema_id = s.schema_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 t.name asc