Get list of table record count

This SQL query will provide a list of tables in a database with the number of rows

select schema_name(tab.schema_id) + '.' + as [tablename],
       sum(part.rows) as [rowcount]
   from sys.tables as tab
        inner join sys.partitions as part
            on tab.object_id = part.object_id
where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
group by schema_name(tab.schema_id) + '.' +
order by sum(part.rows) desc

Each row represents each table. This will also include all the tables in the a database even the empty ones.