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 ?'
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 email@example.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.