How Many Rows Are In That Table?

A question I ask myself often when exploring unfamiliar data sets. So here’s a quickie:

SELECT SchemaName = OBJECT_SCHEMA_NAME([object_id]),
	TableName = OBJECT_NAME([object_id]),
	NumRows = SUM(row_count)
FROM sys.dm_db_partition_stats p
WHERE p.index_id IN (0, 1)
	AND p.[object_id] = OBJECT_ID('dbo.YourTableHere')
GROUP BY [object_id];

If you’re in need of a quick measuring stick, this should do the job.

Microsoft does note that the row_count field is an approximate value in this DMV. It’s taken from the internal table PARTITIONCOUNTS which can be cached, but for everyday use it’s as accurate as you’ll need without COUNT(*).

The rows value in the catalog view sys.partitions is another option. It’s also an approximate value, but tends to be a little more stale due to using the internal ALUCOUNT table. That’s deeper than I’ve needed to dive for row counts.

That’s all there is to it, enjoy. This should cover you for those quick checks, and if you need something pinpoint accurate, you can fall back to COUNT(*).

If you find this handy and you’re after slightly more details about your tables, here’s another larger option to consider which I compiled last year.