Andrew Peterson has a script to get row counts for each table based on sys.dm_db_partition_stats:
When you need the row count from a table, the most common way is to use count(*). But what if you need the row count for all the tables in your database? You could use the sp_MSforeachtable stored procedure:
EXEC sys.sp_MSforeachtable ‘select ”?”,count(*) from ?;’;
Nice, but the return format is a bit difficult to manage, since it effectively runs the query for as many times as you have tables in your database.
Click through for Andrew’s script. One thing to keep in mind is that the number of rows might be off, especially for columnstore tables with ghost records. It’s an estimate, but one which tends toward the correct answer.