I have written following script every time when I am asked by our team leaders or managers that how many rows are there in any particular table or sometime I am even asked which table has highest number of rows. Being Sr. Project Manager, sometime I just write down following script myself rather than asking my developers.
This script will gives row number for every table in database.
USE AdventureWorks
GO
SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count
FROM sys.dm_db_partition_stats st
WHERE index_id < 2
ORDER BY st.row_count DESC
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
41 Comments. Leave new
Nice trick there. I’m saving this one for later uses. Thanks!
How do you do the same in SQL 2000 ?
Great solution!!
Hello Pinal!
i was talking to my brother about this post, and as other readers we proved this script not to work on SQL Server 2000, because this older DBMS does not have the object sys.dm_db_partition_stats :(
Fortunally we came up with this little alternative scrit that works (we think) perfectly on both DBMS.
Tell us what you think about it, and what are the pros and cons vs the one you posted.
Thanks in advance ;)
SELECT OBJECT_NAME(id),rowcnt
FROM SYSINDEXES
WHERE OBJECTPROPERTY(id,’isUserTable’)=1 AND indid < 2
ORDER BY rowcnt DESC
Best Regards,
Afonso.
One can count the numbers of rows in a table by using
count(@@rowcount) as mentioned here–
select count(@@rowcount) from TableName
Thanks! very helpful.
Hi,
How can I find the total number of rows in a specific table without using query like SELECT COUNT(*) FROM TableName
Hi,
How can I retrieve the rows of a table where any column of the row contains NULL, while I have no knowledge about the any column name of the table
Start with this
@Neeraj. (First Post)
Do you mind reading the post, for which you have written a comment.
Please read Pinal Dave post on this web page.
~IM.
Great code, simple and useful
count is mismatching in some tables(large size db)
If you use versions prior to 2005, you need to use
DBCC updateusage(dbname)
for correct result
See this
Thank you! Very useful script.
Very helpful, thank you!
Thanks Pinal (and Afonso), really useful
Pinal,
Your query does not take account of the Schema name. There can be tables having same name but different schema in a database. Also if a table is partitioned, you want to get one row of result 1.e; sum of rows in different partitions. So I have a modified query that I use.
select sc.name +’.’+ ta.name
,sum(pa.rows) — Approximate value, oh well
from sys.tables ta
inner join sys.partitions pa
on pa.object_id = ta.object_id
inner join sys.schemas sc
on ta.schema_id = sc.schema_id
where ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
group by sc.name,ta.name
ORDER BY sum(pa.rows) DESC
your articles are always helpfull
Thank you
Very helpfull… Thank You…
Thanks Pinal, works great!
Hi Pinal,
Using your above query, it gives duplicate table names if the tables are partitioned.
How about the below one..
SELECT
OBJECT_NAME(P.[object_id]) AS [Table Name]
,SUM(rows) AS [No. of rows]
FROM sys.partitions AS P
INNER JOIN sys.tables AS T
ON T.[object_id] = P.[object_id]
WHERE index_id IN (0,1) AND T.[type] = ‘U’
GROUP BY P.[object_id]
ORDER BY 2 DESC
Thank you..
Dave Pinal, you REALLY should update the main article to use something like what Books Online shows:
SELECT SUM (row_count) AS total_number_of_rows
FROM sys.dm_db_partition_stats
WHERE object_id=OBJECT_ID(‘HumanResources.Employee’) AND (index_id=0 or index_id=1);
That query handles multiple partitions, and handles the schema name correctly. It will show the results for a heap, or a table with a primary key. This has been mentioned in the comments a few times, and you say “thanks”, but it would help if you could update the article itself! The code in the article is not quite correct.
David Walker