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

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

Reference : Pinal Dave (https://blog.sqlauthority.com)

SQL Index, SQL Scripts, SQL System Table, SQL Utility
Previous Post
SQLAuthority News – Humor – Favorite Website – Funny Image
Next Post
SQL SERVER – Find Number of Rows and Disk Space Reserved – Using sp_spaceused Interesting Observation

Related Posts

41 Comments. Leave new

  • 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

    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

  • venkatkommineni.k@gmail.com
    January 21, 2013 12:00 am

    HI Pinal,
    You did good job.

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

      • Note that sp_MSforeachtable is undocumented stored procedure and you need to keep this in mind

  • Pinal, How to count column of a table in sql server?

    • Do you want to know total number of columns in a table? If so, use this

      select count(*) from INFORMATION_SCHEMA.COLUMNS 
      WHERE table_name='your table'
  • thanks for this information. explained well and quick

  • 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

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

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

    • You can apply the filter like below

      WHERE OBJECT_NAME(OBJECT_ID) in (‘table1′,’table2′,…..,’table100’)


Leave a Reply