Get Row Count Of All Tables In SQL Server Database

For the purpose of audit and performance improvement planning, I needed a list of all the tables in a database along with the number of records each table has. Below is the simple SQL statement I’ve used to get the row count of all tables in a database. This query will work with SQL Server 2008 and higher.

Query to get row count of all tables

Use WideWorldImporters
SELECT
	ST.name AS Table_Name,
	SUM(DMS.row_count) AS NUMBER_OF_ROWS
FROM
	SYS.TABLES AS ST
	INNER JOIN SYS.DM_DB_PARTITION_STATS AS DMS ON ST.object_id = DMS.object_id
WHERE
	DMS.index_id in (0,1)
GROUP BY ST.name

Query to get row count of all tables along with partition details

To get the partition details of all the tables and the number of records in each partition, you just need to remove the group by clause from the above query and add either partition_id or partition_number from the sys.dm_db_partition_stats view in the select list. Below is the sample query. This query will return the table name the partition ID and the name and the number of records in each partition.

Use WideWorldImporters
SELECT
	ST.name AS Table_Name,
	DMS.partition_id, 
	DMS.partition_number,
	DMS.row_count AS NUMBER_OF_ROWS
FROM
	SYS.TABLES AS ST
	INNER JOIN SYS.DM_DB_PARTITION_STATS AS DMS ON ST.object_id = DMS.object_id
WHERE
	DMS.index_id in (0,1)

Related Article

• Search and find the tables by its full or partial name.
• Search and find the Indexes on a table.