Click here to Skip to main content
15,918,808 members
Articles / Database Development / SQL Server
Tip/Trick

Counting All Rows of All Tables in a Database

Rate me:
Please Sign up or sign in to vote.
4.11/5 (4 votes)
28 Sep 2015CPOL 14.3K   4   9
This tip shows three alternatives (an intuitive and less efficient, a more efficient one and a third more efficient one) to count 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:

SQL
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: 

SQL
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:

SQL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
Portugal Portugal
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Gaston Verelst29-Sep-15 20:13
Gaston Verelst29-Sep-15 20:13 
QuestionImportant Post Pin
A. Rocha28-Sep-15 23:22
A. Rocha28-Sep-15 23:22 
GeneralNothing new Pin
wmjordan28-Sep-15 14:50
professionalwmjordan28-Sep-15 14:50 
GeneralRe: Nothing new Pin
Antonio Soares28-Sep-15 22:35
professionalAntonio Soares28-Sep-15 22:35 
GeneralRe: Nothing new Pin
A. Rocha28-Sep-15 23:31
A. Rocha28-Sep-15 23:31 
GeneralMy vote of 1 Pin
Member 1102269128-Sep-15 11:52
Member 1102269128-Sep-15 11:52 
GeneralRe: My vote of 1 Pin
Antonio Soares28-Sep-15 22:36
professionalAntonio Soares28-Sep-15 22:36 
GeneralRe: My vote of 1 Pin
A. Rocha28-Sep-15 23:31
A. Rocha28-Sep-15 23:31 
GeneralRe: My vote of 1 Pin
Member 1102269119-Oct-15 1:19
Member 1102269119-Oct-15 1:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.