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

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
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

rowcount SQL SERVER   Find Row Count in Table   Find Largest Table in Database   T SQL

Reference : Pinal Dave (http://blog.SQLAuthority.com)

41 thoughts on “SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL

  1. 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?


  2. 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
    WHERE OBJECTPROPERTY(id,’isUserTable’)=1 AND indid < 2
    ORDER BY rowcnt DESC

    Best Regards,


  3. 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


  4. @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.



  5. 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


  6. Pingback: SQL SERVER – Find Row Count in Table – Find Largest Table in Database – Part 2 Journey to SQL Authority with Pinal Dave

  7. Hi Pinal,

    Using your above query, it gives duplicate table names if the tables are partitioned.

    How about the below one..

    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]

    Thank you..


  8. 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


  9. Can someone help me with my problem?
    My table looks like this

    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


  10. Pingback: SQL SERVER – Identify Most Resource Intensive Queries – SQL in Sixty Seconds #029 – Video « SQL Server Journey with SQL Authority

  11. Pingback: SQL SERVER – Weekly Series – Memory Lane – #012 « SQL Server Journey with SQL Authority

  12. 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 [‘])


  13. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  14. sir
    i have assignment table in that table assigmnet table iam going to assign some requirements for all the recruters but iam going to assign multiple requirements for single one how to calculate how many records he have in that table
    using sql server


  15. An interesting discussion is worth comment. There’s no doubt that that you should publish more on this subject matter, it might not be a taboo subject but generally people do not discuss such issues.
    To the next! Kind regards!!


  16. Hi Pinal,

    I have totally 1000 tables in a database . Now i have to count table wise row count that should be for 100 tables only . so how to write a query for that…


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s