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.

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

See the below result screenshot that returns the above query.

All tables' row count screenshot.

screenshot