Counting Rows of a Database Table Wise in SQL Server

In SQL Server, you can count the rows of a database table wise using the COUNT(*) function or the sys.partitions system view.

Syntax:

-- Using COUNT(*) function 
SELECT TABLE_NAME, COUNT(*) AS RowCount FROM INFORMATION_SCHEMA.TABLES INNER JOIN TABLE_NAME ON INFORMATION_SCHEMA.TABLES.TABLE_NAME = TABLE_NAME GROUP BY TABLE_NAME; 
-- Using sys.partitions system view 
SELECT OBJECT_NAME(p.object_id) AS TABLE_NAME, SUM(p.rows) AS RowCount FROM sys.partitions p WHERE p.index_id IN (0,1) GROUP BY p.object_id; 

Example 1: Counting Rows using COUNT(*) function

Let's say we have a database with multiple tables, and we want to count the rows of each table.

Query:

SELECT TABLE_NAME, COUNT(*) AS RowCount FROM INFORMATION_SCHEMA.TABLES INNER JOIN sys.tables ON INFORMATION_SCHEMA.TABLES.TABLE_NAME = sys.tables.name GROUP BY TABLE_NAME; 

Result:

The result will display the table name and the count of rows for each table.

Example 2: Counting Rows using sys.partitions system view

Let's say we have a database with multiple tables, and we want to count the rows of each table using the sys.partitions system view.

Query:

SELECT OBJECT_NAME(p.object_id) AS TABLE_NAME, SUM(p.rows) AS RowCount FROM sys.partitions p WHERE p.index_id IN (0,1) GROUP BY p.object_id; 

Result:

The result will display the table name and the count of rows for each table.