Get list of tables with total row number

Imagine that a customer calls you and complains that your application is becoming too slow.
There might be tons of reasons for that, in some cases it may be your database has grown too big.

one thing that may be useful to you is to know the size of tables, in this case we measure it by checking the number of rows, not the actual size occupied.

Here is the select you may want to use:

SELECT SCHEMA_NAME(A.schema_id) + '.' +
 A.Name, SUM(B.rows) AS 'RowCount'
FROM sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
WHERE A.type = 'U'
GROUP BY A.schema_id, A.Name
order by SUM(B.rows) desc