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 (http://blog.SQLAuthority.com)
Nice trick there. I’m saving this one for later uses. Thanks!
Hi Pinal,
I wanted to know that is there any query to delete a particular column value from a table.
Or any other alternative for the same.
Suppose I have a row as
Id Name Grade
1 Guru A
Now if I only want to delete Grade-’A',
Can I do that?
Only way is to update the table to have Grade to NULL
Update table
Set Grade=NULL
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
@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
http://beyondrelational.com/blogs/madhivanan/archive/2007/11/02/different-ways-to-count-rows-from-a-table.aspx
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
Thanks!
[...] 8, 2010 by pinaldave Last Year I wrote article on the subject SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL. It is very good to see excellent participation there. In my script I had not taken care of table [...]
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
Followup: If you want to get the number of records for each table, you need to group by object ID and then sum for each group.
man u are ossom….
Can someone help me with my problem?
My table looks like this
CID FNAME MIN MAX COM
A OP1 0 23 5
A OP1 24 35 2
A OP1 36 99 1
A OP2 0 23 5
A OP2 24 35 2
A OP2 36 99 0
A OP3 0 23 5
A OP3 24 35 2
A OP3 36 99 1
B OP1 0 23 9
B OP1 24 99 2
B OP2 0 23 9
B OP2 24 99 2
B OP3 0 23 7
B OP3 24 35 3
B OP3 36 99 1
The expected duplicate results i need are:
A OP1
A OP3
B OP1
B OP2
thanks
how can i get every table rows base on a snapshot? please help
[...] Find Row Count in Table – Find Largest Table in Database – T-SQL [...]
[...] Find Row Count in Table – Find Largest Table in Database – T-SQL There is more than one way to find the information from the table regarding how many total rows it contains. In this blog post, we go over another method which is very rarely used but when used it gives very accurate data very quickly. If you are the one who loves new interesting details, this blog post is specifically for you. [...]
HI Pinal,
You did good job.
Kommineni
Most of suggested solutions will work for sure but I suggest this:
EXEC sp_spaceused ‘Table_Name’
It gives some other useful information as well! Hope it helps :)
Just in case you want to get row counts for all tables in a database, you can run the following:
exec sp_MSforeachtable ‘exec sp_spaceused ”?”’
(all quotes are single quotes ['])