Anyone who has worked with a database for even a short amount of time has undoubtedly checked the number of rows in a table with a statement akin to this:

SELECT COUNT(*) FROM dbo.TableName

It’s quick and easy, but is expensive in terms of logical reads. Also, things get complicated when you want to check the numbers of rows for all user tables in an entire database – you might think about resorting to cursors and dynamic SQL.

Fortunately, SQL Server 2005 shipped with a whole slew of Dynamic Management Views that make monitoring your database much simpler. With a quick query, we can view the rowcount for all tables in a database – and no table scan required.

SELECT s.name [SchemaName], t.name [TableName], SUM(st.row_count) [RowCount]
FROM sys.tables t
INNER join sys.schemas s ON t.schema_id = s.schema_id
INNER join sys.indexes i ON t.OBJECT_ID = i.OBJECT_ID
INNER join sys.dm_db_partition_stats st ON t.OBJECT_ID = st.OBJECT_ID and i.index_id = st.index_id
WHERE i.index_id < 2
GROUP BY s.name, t.name
ORDER BY s.name, t.name

Turn that into a handy view and you’ll always know where you stand on rows per table.