Using SQL Server Metadata and Statistics to Build a Table

select o.name as [Name], sc.name as [Type], s.row_count as [Size]
 from sys.objects o        
left join sys.schemas sc on sc.schema_id = o.schema_id
left join (select object_id, sum(rows) as row_count from sys.partitions 
      where index_id < 2
    group by object_id) s on o.object_id = s.object_id
where o.type = 'U'
order by sc.name, o.name