Post
Ask Question

Get Numbers of Records in Each Table

In this blog we will learn how to count the numbers of records for each table in SQL Server. We will use the below query to find out the records for all tables.

Query:

  1. SELECT t.name AS Table_Name, I.rows AS Record FROM  
  2.   
  3. sys.sysindexes I  
  4.   
  5. INNER JOIN sys.tables t  
  6.   
  7. ON  
  8.   
  9. t.object_id=I.id WHERE I.indid<2  

Example:

 

Note:   You can also use this query to find out the numbers of records for a particular table by providing the table name as below.

  1. SELECT t.name AS Table_Name, I.rows AS Record FROM  
  2.   
  3. sys.sysindexes I  
  4.   
  5. INNER JOIN sys.tables t  
  6.   
  7. ON  
  8.   
  9. t.object_id=I.id WHERE I.indid<2 AND  
  10.   
  11. t.name=Table_Name'  

But I recommend to use "SELECT COUNT(1) as Records FROM Table_Name' query when you want to get the records for a particular table because complexity above the query is very high. So only use this query when you want to get the numbers records from each table.
Next Recommended Reading Limits the Number of Records Per Page in SSRS 2012