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

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

About these ads

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

    Best Regards,
    Afonso.

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

    select count(@@rowcount) from TableName

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

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

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

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

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

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

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

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

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

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

  14. 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 ['])

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

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