SQL SERVER – How to Find Row Count of Every Table in Database Efficiently?

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)