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!!