Question: How to Find Row Count of Every Table in Database Efficiently?
Answer: There are some questions which are evergreen. I recently asked this question in the interview and user answered me that I can find this by looping over every single table in a cursor. I further gave him hint that he can think beyond the box and think of any other alternative solution for this problem.
Interesting enough, he did not come up any further creative idea. I was expecting that he may say something like – Statistics, DMV or something in that line. I was no way expecting the correct and complete answer from him in the interview.
Here is a script when you run it, it gives all the tables in the database with the table row count.
SELECT SCHEMA_NAME(schema_id) AS [SchemaName], [Tables].name AS [TableName], SUM([Partitions].[rows]) AS [TotalRowCount] FROM sys.tables AS [Tables] JOIN sys.partitions AS [Partitions] ON [Tables].[object_id] = [Partitions].[object_id] AND [Partitions].index_id IN ( 0, 1 ) -- WHERE [Tables].name = N'name of the table' GROUP BY SCHEMA_NAME(schema_id), [Tables].name;
When you run this query, it will give you following result, which is pretty accurate and much faster than going over every single table to find the row count.
Let me know if you know any other such tracks.
Reference: Pinal Dave (http://blog.SQLAuthority.com)
42 Comments. Leave new
worked correctly. Thanks a lot
For few tables i am seeing the output as 1000 but those tables have rows like 114K. This was happening for many tables
Thanks a bunch!
I’ve found the best way is to right click the database in SSMS object explorer. Then select:
Reports\Standard Reports\Disk Usage By Top Tables.
Thank you!
nice query, very useful
Yes thank you!
This works beautifully. Is there any way to add a where clause into the select statement here? So count rows where the created date for that row might be greater than a certain date for example?
excellent! It worked without any issues
thanks for this script
Was excited about this query, but unfortunately many of the tables have row count of 1000
Hi Mark – If you’re seeing incorrect results then it may be that the storage stats are stale. A few people have commented on that issue over time. You could try running a DBCC UPDATEUSAGE on the database and see if the partition row counts are fixed by that. As Pinal Dave commented earlier, how things get into that state in the first place is best known to Microsoft!
Interview Question:-
How to efficiently pull data from a database with 100 tables?
Thank you.
Thank you! Worked a treat!