SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL

Feeds:: Posts; Comments

SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL

January 13, 2009 by pinaldave

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)

Posted in Pinal Dave, SQL, SQL Authority, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL System Table, SQL Tips and Tricks, SQL Utility, T SQL, Technology | 12 Comments

12 Responses

on January 14, 2009 at 3:12 am | Reply Celerian

Nice trick there. I’m saving this one for later uses. Thanks!
on January 14, 2009 at 5:03 pm | Reply Hiten

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?
on January 16, 2009 at 1:17 am | Reply Ganesh Gopal

How do you do the same in SQL 2000 ?
on January 21, 2009 at 7:28 am | Reply Uday

Great solution!!
on January 22, 2009 at 4:11 pm | Reply Afonso Mata

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.
on August 25, 2009 at 6:32 pm | Reply Jolly

One can count the numbers of rows in a table by using
count(@@rowcount) as mentioned here–

select count(@@rowcount) from TableName
on September 12, 2009 at 1:30 am | Reply Frank

Thanks! very helpful.
on September 19, 2009 at 12:03 pm | Reply Neeraj

Hi,
How can I find the total number of rows in a specific table without using query like SELECT COUNT(*) FROM TableName
on September 19, 2009 at 12:05 pm | Reply Neeraj

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
on September 20, 2009 at 11:03 am | Reply Imran Mohammed

@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.
on September 21, 2009 at 5:31 am | Reply glyquiroz

Great code, simple and useful
on October 20, 2009 at 2:04 pm | Reply Ajithamalar

count is mismatching in some tables(large size db)