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

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

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

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

    Reply
  • 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.

    Reply
  • man u are ossom….

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

    Reply
  • thanks

    Reply
  • how can i get every table rows base on a snapshot? please help

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

    HI Pinal,
    You did good job.
    Kommineni

    Reply
  • 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 :)

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

      Reply
      • 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?

    Reply
    • 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'
      
      Reply
  • thanks for this information. explained well and quick

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

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

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

    Reply
    • You can apply the filter like below

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

      Reply

Leave a Reply

Menu