Counting All Rows of All Tables in a Database

Introduction

Probably, using SQL Server, you might need to know the number of rows of all tables in a database, to get a statistic of the huge tables this database has, or even to know tables that are not used at all. This tip shows three alternatives to do that.

Using the Code

An intuitive way to count all rows of all tables in a database (and also less efficient) is to add a cursor for select all object names that are tables and for each name, to build a SQL statement that counts the number of rows:

declare @schema_name as varchar(200), @table_name as varchar(200)
declare @sql_statement as varchar(MAX)
DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)

declare table_cursor cursor for
SELECT s.name as [schema_name], t.name as table_Name
FROM sys.tables as t
     JOIN sys.schemas as s
       ON t.schema_id = S.schema_id
ORDER BY s.name

open table_cursor
FETCH NEXT FROM table_cursor 
INTO @schema_name, @table_name

WHILE @@FETCH_STATUS = 0
BEGIN
   FETCH NEXT FROM table_cursor INTO @schema_name, @table_name
   set @sql_statement = concat(@sql_statement, 'select ''' + @schema_name + _
	''' as SchemaName, ''' + @table_name + _
	''' as TableName, count(*) as Count from ' + _
	@schema_name + '.' + @table_name + @NewLineChar)
   IF @@FETCH_STATUS = 0
   SET @sql_statement = concat(@sql_statement, 'UNION ')
END
CLOSE table_cursor;
DEALLOCATE table_cursor;

set @sql_statement = concat(@sql_statement, ' ORDER BY Count DESC')
EXEC(@sql_statement)

A more efficient way of doing this is by using the sysindexes table, which rows column has exactly the number of rows of the respective table, given by sysobject's name column: 

SELECT
    sysobjects.name, sysindexes.rows
FROM
    sysobjects INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE
    sysobjects.type = 'U' AND sysindexes.indid < 2
ORDER BY
    sysindexes.rows desc

A more efficient way of doing this is by using the dm_db_partition_stats table:

SELECT
  sys.objects.name AS Name,
  sys.dm_db_partition_stats.row_count AS Rows
FROM sys.dm_db_partition_stats
INNER JOIN sys.objects
  ON sys.dm_db_partition_stats.object_id = sys.objects.object_id
WHERE sys.objects.type = 'U'
AND sys.dm_db_partition_stats.index_id < 2
ORDER BY sys.dm_db_partition_stats.row_count DESC