How many records in each table?

Here's a quick way to find the number of records in each table of a SQL Server database. Use the sp_msforeachtable stored procedure to iterate thru each table, like this:

sp_msforeachtable 'select count(*) from ?' 

Reader feedback

Cary F. writes: While Parthasarathy Mandayam's tip was useful to me, I found a slightly more useful version. I needed not only the number of records in each table, but the name of the SQL Server table, too. Still using the sp_msforeachtable stored procedure, use the following code:

sp_msforeachtable 'select "?", count(*) from ?' 

This returns the table name and the number of records in one fell swoop.

John L. writes: Unfortunately, this tip only seems to work on V7 of SQL Server. It does not work in SQL Server 2000. You get the following error:

Server: Msg 207, Level 16, State 3, Line 1 
Invalid column name '[dbo].[Orders]'.

Paul R. writes: The reason that John L. (above) had a problem is because SQL Server 2000 treats double quotes as a column name. The following SQL will work on SQL Server 2000:

sp_msforeachtable 'select ''?'', count(*) from ?' 

Note that these are not double quotes, but rather two single quotes.

Peter J. writes: When the statistics in your database are up to date, then the following query returns the same results without needing to do a table scan on every table in the database. Doing a count(*) on every table might be a nightmare on a datawarehouse!

SELECT a.name, b.rows 
FROM sysobjects a, sysindexes b 
WHERE a.id = b.id AND b.indid <= 1 AND a.type = 'U' 
AND a.name <> 'dtproperties' 
ORDER BY a.name

For More Information

• What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
• The Best SQL Server Web Links: tips, tutorials, scripts, and more.
• Have an SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
• Check out our Ask the Experts feature: Our SQL, Database Design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.