Avoid overhead with this completely awesome SQL Server query that returns the row-count of all tables without actually having to query each table. Instead, it just pulls from the statistics:
SELECT ‘[‘ + SCHEMA_NAME(t.schema_id)+ ‘].[‘ + t.name + ‘]’ AS full_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
t.name AS table_name,
i.rows
FROM sys.tables AS t INNER JOIN
sys.sysindexes AS i ON t.object_id =i.id AND i.indid < 2