How do I get a count of all rows from every table?

by Andrew Taylor on Jan.08, 2010, under SQL Server 2008 Tip & Tricks

In previous versions of SQL Enterprise Manager you could simply click on a database and see how large each table was and how many rows were contained within it. For some reason Microsoft decided not to add this functionality starting with SQL Server 2005 and the SQL Management Studio. I recently needed to use this functionality for a report and was unable to find any good examples on the internet. I found examples that either used a method that wasn’t sortable or that only checked records in tables that have clustered indexes. While this would generally be good enough as you usually have a clustered index, there are times when you have a database that may have unclustered tables. I also wanted something that would give me more functionality possibilities down the road if I needed it.

The Simplest List:

This code will generate a very simple list with 1 record set for each table.

DECLARE @TABLENAME VARCHAR(255)


DECLARE db_cursor CURSOR FOR

SELECT name

FROM sysobjects WHERE xtype = 'U' ORDER BY name
OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @TABLENAME 
WHILE @@FETCH_STATUS = 0

BEGIN 
	EXEC ('SELECT ''' + @TABLENAME + ''' AS TableName, COUNT(*) AS RecordCount FROM ' + @TABLENAME)
    FETCH NEXT FROM db_cursor INTO @TABLENAME
END

CLOSE db_cursor DEALLOCATE db_cursor

All this code does is cursor through each table and select a count for that table.

More Complex but Easier Results:

This query gives you a single resultset with a list of all tables and their counts. This could be expanded out to provide additional information for example you could add extra columns to gather specific table information.

DECLARE @TABLENAME VARCHAR(255)


CREATE TABLE #TABLECOUNTS

(

	TableName varchar(255),

	RecordCount int

)
DECLARE db_cursor CURSOR FOR

SELECT name

FROM sysobjects WHERE xtype = 'U' ORDER BY name
OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @TABLENAME 
WHILE @@FETCH_STATUS = 0

BEGIN 
	EXEC ('INSERT INTO #TABLECOUNTS (TableName, RecordCount) SELECT ''' + @TABLENAME + ''' AS TableName, COUNT(*) AS RecordCount FROM ' + @TABLENAME)
    FETCH NEXT FROM db_cursor INTO @TABLENAME
END
CLOSE db_cursor

DEALLOCATE db_cursor 
SELECT * FROM #TABLECOUNTS