Post
Ask Question

Getting Number Of Table Rows In SQL Server

Often we need to check the no. of rows per table in SQL Server. Suppose your database is huge and you need a report no. of rows per table, then this blog will help you to find out the same.
 
We can achieve the same in the following ways:
 
Option 1: Using predefined tables sys.tables, sys.partitions, sys.allocation_units
  1. SELECT  
  2.     t.NAME AS TableName,  
  3.        p.[Rows],  
  4.     i.name as indexName,    
  5.     sum(a.total_pages) as TotalPages  
  6. FROM  
  7.     sys.tables t  
  8. INNER JOIN       
  9.     sys.indexes i ON t.OBJECT_ID = i.object_id  
  10. INNER JOIN  
  11.     sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id  
  12. INNER JOIN  
  13.     sys.allocation_units a ON p.partition_id = a.container_id  
  14. WHERE  
  15.     t.NAME NOT LIKE 'dt%' AND  
  16.     i.OBJECT_ID > 255 AND    
  17.     i.index_id <= 1  
  18. GROUP BY  
  19.     t.NAME, i.object_id, i.index_id, i.name, p.[Rows]  
  20. ORDER BY  
  21.     object_name(i.object_id)  
 Option 2: Using stored procedure sp_MSForEachTable
  1. sp_MSForEachTable 'DECLARE @t AS VARCHAR(MAX);  
  2. SELECT @t = CAST(COUNT(1) as VARCHAR(MAX))  
  3. + CHAR(9) + CHAR(9) + ''?'' FROM ? ; PRINT @t'  
Option 3: Using sysobjects, sysindexes
  1. SELECT  
  2.     sysobjects.Name  
  3.     , sysindexes.Rows  
  4.     , sysindexes.IndId  
  5. FROM  
  6.     sysobjects  
  7.     INNER JOIN sysindexes  
  8.     ON sysobjects.id = sysindexes.id  
  9. WHERE  
  10.     sysobjects.type = 'U'  
  11.     AND sysindexes.IndId < 2  
Output 
 
Figure 1: Displays no of Rows per table
 
Hope this blog helps you to find out no. of rows per table in SQL Server.
 
Happy Coding!!