How To Get Table Row Counts Quickly and Easily

Generally we are used SELECT COUNT(*) statement make a full table scan to return the total table's row count, it can take more time for the large table. 

The alternate way to determine the total row count in a table. 

1. we can use sysindexes system table, in this case. There is ROWS column in the sysindexes table. This column contains the total row count for each table in your database. So, we can use the following select statement instead of SELECT COUNT(*): 

Query 1:
 
SELECT rows FROM sysindexes WHERE id = OBJECT_ID('table_name') AND indid < 2 

In the similar way we can find the all table row counts in a current database by using the following query

Query 2:
 

SELECT so.NAME,si.rowcnt FROM sysindexes AS si  

INNER JOIN sysobjects AS so ON si.id = so.id  

WHERE si.indid < 2 AND OBJECTPROPERTY(so.id, 'IsMSShipped') = 0 
ORDER BY so.NAME

Note:

Remove is_ms_shipped = 0 check to include system objects 
i.index_id < 2 indicates clustered index (1) or hash table (0) 

If we are using SQL 2005 or 2008 querying sysindexes will still work but Microsoft advises that sysindexes may be removed in a future version of SQL Server so as a good practice we should use the DMVs (Dynamic Management Views For more details Click Here) instead, like so:

Query 3:
 
Row Count Using DMV's:
  

SELECT o.name,ddps.row_count FROM sys.indexes AS i 
 INNER JOIN sys.objects AS o ON i.OBJECT_ID = o.OBJECT_ID 
 INNER JOIN sys.dm_db_partition_stats AS ddps ON i.OBJECT_ID = ddps.OBJECT_ID 
 AND i.index_id = ddps.index_id 
WHERE i.index_id < 2 AND o.is_ms_shipped = 0 
ORDER BY o.NAME