How To Get Table Row Count In SQL Server

Recently, I got the requirement to get the table row count for some of the tables in my SQL Server database. In this article, I will walk you through all the possible approaches to get table row count in SQL Server.

How To Get Table Row Count In SQL Server

Let us discuss all the possible approaches individually.

Approach-1: Using COUNT(*)

To get table row count in SQL server, execute the below SQL query using the Count(*) function.

SELECT COUNT(*) AS MyTableRowCount
FROM [Product];

After executing the above query, I got the output, as shown in the screenshot below.

Approach-2: Using sys.partitions

We can query the sys.partitions using the below query for this purpose.

select sum([rows]) as Totalrowcount
from sys.partitions
where object_id=object_id('Product')
 and index_id in (0,1)

After executing the above query, I got the expected output, as shown in the screenshot as shown below.

Approach-3: Using sys.dm_db_partition_stats

We can also query the sys.dm_db_partition_stats using the below SQL query.

SELECT SUM (row_count) as TableRowCount
FROM sys.dm_db_partition_stats 
WHERE object_id=OBJECT_ID('Product')    
AND (index_id=0 or index_id=1)

After executing the above query, I got the expected output, as shown in the screenshot shown below.

Approach-4: Using the sp_spaceused stored procedure

We can execute the sp_spaceused stored procedure for this purpose.

EXEC sp_spaceused 'Product';

After executing the above query, I got the expected output, as shown in the screenshot shown below.

Approach-5: Using @@ROWCOUNT

We can use the @@ROWCOUNT for this purpose.

SELECT * FROM [Product];
SELECT @@ROWCOUNT AS TableRowCount;

After executing the above query, I got the expected output, as shown in the screenshot below.

Approach-6: Using sys.objects

We can also query the sys.objects to get the total counts of each table in your database.

select a.name, s.rowcnt 
from sys.objects a join sys.sysindexes s 
on a.object_id = s.id
where a.is_ms_shipped = 0
and s.rowcnt > 0
order by a.name

After executing the above query, I got the expected output, as shown in the screenshot shown below.

Approach-7: Using sys.tables and sys.sysindexes

We can also query the sys.tables and sys.sysindexes with the help of inner join with the help of the below query to get the row counts of each table in my SQL database.

SELECT 
    n.NAME AS MyTableName,
    r.rows AS TableRowCounts
FROM 
    sys.tables n
INNER JOIN 
    sys.sysindexes r ON n.OBJECT_ID = r.ID
WHERE 
    r.indid < 2 AND n.is_ms_shipped = 0
ORDER BY 
    r.rows DESC;

After executing the above query, I got the expected output, as shown in the screenshot shown below.

Conclusion

When you have the requirement to get the total size of your SQL data then it is essential to know the row count in SQL server table. You can use any of the approaches mentioned in this article as mentioned in this article.

You may also like following the articles shown below.

• How To Get Column Description In SQL Server
• Delete Duplicate Rows in SQL Server
• SQL Server Row_Number Join
• How To Get Month Name From Date In SQL Server