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

  • Nice trick there. I’m saving this one for later uses. Thanks!

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

    Reply
  • How do you do the same in SQL 2000 ?

    Reply
  • Great solution!!

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

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

    select count(@@rowcount) from TableName

    Reply
  • Thanks! very helpful.

    Reply
  • Hi,
    How can I find the total number of rows in a specific table without using query like SELECT COUNT(*) FROM TableName

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

    Reply
    • Start with this

      select count(*) from table
      where col1 is null or col2 is null or ....... colN is null
      
      Reply
  • @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.

    Reply
  • Great code, simple and useful

    Reply
  • count is mismatching in some tables(large size db)

    Reply
    • If you use versions prior to 2005, you need to use

      DBCC updateusage(dbname)

      for correct result

      See this

      Reply
  • Seth Delconte
    March 2, 2010 10:30 pm

    Thank you! Very useful script.

    Reply
  • Very helpful, thank you!

    Reply
  • Thanks Pinal (and Afonso), really useful

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

    Reply
  • your articles are always helpfull

    Thank you

    Reply
  • Very helpfull… Thank You…

    Reply
  • Thanks Pinal, works great!

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

    Reply

Leave a Reply