A quick post that proved helpful to me today. I needed to see the number of rows for all tables in the database. I found this query:
SELECT ‘[‘ + SCHEMA_NAME(t.schema_id) + ‘].[‘ + t.name + ‘]’ AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name,
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2
on Brian Knights blog: Quickly Retrieving the Row Count for All Tables. One thing to note is that the numbers could be a little out of date. DBCC UPDATEUSAGE (DBNAME) should resolve that, the problem is that this only works when you are admin. If you want to be 100% accurate you might need to do a little more investigation.