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
Thanks a lot Sir for sharing the info :)
Wonderful Answer. However I found out that for running this command we need SA user rights. Is it correct or only happening in my case?
Thank you sir for sharing the script. I’m using the below script to find the Row Count of all tables in a Database. But the script in this post was much efficient than the below one.
SELECT OBJECT_SCHEMA_NAME(o.id) AS SchemaName, O.NAME, i.rowcnt
FROM sysindexes AS i
INNER JOIN sysobjects AS o ON i.id = o.id
INNER JOIN SYS.TABLES T ON T.object_id = O.id
WHERE i.indid < 2
–AND O.Name = 'Table Name'
ORDER BY o.NAME
Thank you very much for sharing this query..
Ive done this a thousand times in my own simple way.
select ‘select ‘ + char(16) + name + char(16) + ‘, count(*) from ‘ + name + ‘ union ‘ from sys.tables
then i just copy the result into the command window, replace the last ‘union’ with an ‘order by 2 desc’ since im almost always more interested in the most populous tables and run all of it. Sometimes i have to tweak it a little since im only interested in some of the tables and then i just remove those lines.
Sorry if it doesnt work as is… ive been working with other things the last few years and I wrote it from memory…
This works in SQL 2012 and later, I believe: –
SELECT
SCHEMA_NAME([schema_id]) AS [schema]
, [name]
, OBJECTPROPERTYEX([object_id], ‘Cardinality’) AS [count]
FROM
sys.tables
;
Thanks for your posts, been using this site for many years as it keeps coming up in searches for answers!
Using “relative to batch” Execution Plan results, it shows 64% article proposed query versus 36% on Justins query. Nice, seems it is time to stop using my sp_spaceused hack ;-)
How well does Justin’s query work if there are partitioned tables? Honest question…I don’t have a way to test…
what if i have to count rows with where clause
select count(*) FROM bigtable
where aDate between ‘2018-JAN-01 00:00:00’ and ‘2018-JAN-11 23:59:59’
and ur.uDeleted 1
adate having index
can i use DMV with where clause on table.
I do not think you can apply filter and do count using DMV
Why sys.partitions rows values and table count(eg. Select Count(1) from table_name ) not matched
I think that question can be better answered by Microsoft.
This is a really nice article. Thank you. I have a follow up question. How would I go about getting usage statistics for each filed/column in each of these tables?
Counts in sys.partitions are not reliable, use an alternative method.
your query taking longer time can you optimize and repost this?
I just tried it on a huge server with over 1000 records and 1 TB data, the query is running in less than 1 seconds.
I did it on a DB that is 3 TB with 550k tables and even added a sort and it only took a few seconds, maybe a minute., Much better than what I tried!
Select object_name(ID) as [Table] ,
rowcnt as [Rows]
from sys.sysindexes
where indid < 2
The results look unreliable. Comparing the result of the above query and using ‘SELECT COUNT(*) FROM ‘ don’t seem to match for many tables. What could be the reason for the mismatch on the number of rows in tables?
I think its got to do with Tables in External Data Sources.
I reanalyzed and saw that I was getting correct row counts for tables in my Database, except for the tables from the External Data Source.
Could that be because SQL Server doesn’t keep Statistics for External Data Source Tables?
Now imagine that i want a sum for all the row in Adress table with same address. How can i do that?
Hi,
How to write the same query in Mysql workbench,
Please do reply with the query.
Thanks.
Naveen
Hi, great solution! Is it possible to only select tables with a specific name/string?
WHERE [Tables].name LIKE ‘_DIM’
Hope somebody has a solution, because above doesnt work.
Hi, Fantastic Solution.
Thank you for your time on this, really helped when reviewing a cleardown of data from only specific tables.
Thank you
Worked perfectly, many thanks!