Count Rows in all the Tables of a SQL Server Database

To get an accurate value of all the rows in a SQL Server table, use DBCC UPDATEUSAGE. DBCC UPDATEUSAGE corrects the rows, used pages, reserved pages, leaf pages and data page counts for each partition in a table or index. Here’s a query that first uses DBCC UPDATEUSAGE and then count all the rows in all the tables of a database using the undocumented stored procedure sp_msForEachTable

USE AdventureWorks

EXEC sp_msForEachTable
'SELECT PARSENAME(''?'', 1) as TableName,
COUNT(*) as NumberOfRows FROM ?'

OUTPUT (Partial)


About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook


Anonymous said...

Suggestion for a new Blog Title: "How to kill larges Databases 101"

John David said...

Hey..i am also looking for the same topic.
thanks for sharing with us all.

Madhivanan said...

Another method is

select object_name(id) as table_name, rows from sys.sysindexes
where indid<2
order by table_name