How To Get All Row Count For All Tables In SQL Server Database

In this post, we will learn about how to get all tables records count from the selected database. Here we are using sys.objects and sys.partitions for getting the record count. Here we are using join sys.objects with sys.partitions from sys.partitions we can get row count of table and sys.objects will return the name of a schema (table name).

In this post, we will learn how to get all table record counts from the selected database. Here, we are using sys.objects and sys.partitions for getting the record count. Here, we are using join sys.objects with sys.partitions from sys.partitions, we can get row count of table and sys.objects will return the name of a schema (table name).

Here, we are setting the short name A for getting table name and short name B for getting row count. See the below query for getting record count. For this example, set the top 10 rows to get only 10 table names and record counts. See the below example query. Let's start coding.

SELECT TOP 10 (SCHEMA_NAME(A.schema_id) + '.' + A.Name) AS TableName
, SUM(B.rows) AS RecordCount
FROM sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
WHERE A.type = 'U'
GROUP BY A.schema_id, A.Name

See the below result screenshot that returns the above query.

All tables' row count screenshot.