SQL SERVER – Find Row Count in Table – Find Largest Table in Database – Part 2

Last Year I wrote article on the subject SQL SERVER – Find Row Count in Table – Find Largest Table in Database – T-SQL. It is very good to see excellent participation there. In my script I had not taken care of table schema. SQL Server Expert Ameena has modified the same script to include the schema. Here is the new modified script.

SELECT sc.name +'.'+ ta.name TableName
,SUM(pa.rows) RowCnt
FROM sys.tables ta
INNER JOIN sys.partitions pa
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

Above query will return following resultset.

rwcnt SQL SERVER – Find Row Count in Table – Find Largest Table in Database – Part 2

You can see in resultset that tablename is prefixed with Schema name and numbers of the rows are listed in the table.

If you are using earlier version of SQL Server 2000 you may want to run following command to update the pages and its statistics.

DBCC updateusage(adventureworks)

If you have better query to find out table row count, please share here.

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

26 thoughts on “SQL SERVER – Find Row Count in Table – Find Largest Table in Database – Part 2

  1. If you’re using SSMS with SQL Server 2008 you can:
    1. Go to Object Explorer Window and select the Tables folder in the database of interest.
    2. Hit F7 to bring up Object Explorer Details Window.
    3. Right Click on the column headings in the new window and add a column called Row Count (similar to the way Windows Explorer lets you add columns).
    4. Once you’ve added the column click on the refresh button. 5. Then click on the column heading again to sort by # of rows.


  2. I thnk ths is more smple qry for doing this wrk.

    Select a.Name as TblName,Sum(b.Rows) as RowsCount from Sys.Tables as a,Sys.Partitions as b where a.Object_Id=b.Object_Id Group By a.Name Order By Sum(Rows) Desc


  3. Actually, I prefer the SSMS reports. If you right-click on the database in the SSMS and you choose Reports –> Standard Reports and then you choose “Disk Usage by Table” you will get a report which shows not only the row count, but also the Reserved space, the data space, the index space and the unused space. All this information is very important when considering performance, scalability and even future designing of the system. I have seen tables with 100,000 rows which were more problematic than tables with 40 million rows.


  4. SELECT os.name, ob.name, ind.rowcnt
    FROM sys.sysindexes ind
    INNER JOIN sys.objects ob
    ON ind.id = ob.Object_id
    INNER JOIN sys.schemas os
    ON ob.Schema_id = os.Schema_id
    WHERE ob.type=’u’ and indid<2
    ORDER BY ind.rowcnt desc


  5. Whatever happened to good old Microsofts sp_spaceused?

    create table #tablesize ( name varchar(50),
    rows int,
    reserved varchar(20),
    data varchar(20),
    index_size varchar(20),
    unused varchar(20)

    exec sp_msForEachTable ‘insert #tablesize exec sp_spaceused ”?”’

    declare @sortorder tinyint
    set @sortorder = 2

    select name,
    replace(rows, ‘KB’, ”) as Rows,
    convert(int,replace(reserved, ‘KB’, ”)) as TotalReserved,
    convert(int,replace(data, ‘KB’, ”)) as DataSize,
    convert(int,replace(index_size, ‘KB’, ”)) as IndexSize,
    convert(int,replace(unused, ‘KB’, ”)) as Unused
    from #tablesize
    order by case when @sortorder = 1 then Rows
    when @sortorder = 2 then convert(int,replace(reserved, ‘KB’, ”))
    when @sortorder = 3 then convert(int,replace(data, ‘KB’, ”))
    when @sortorder = 4 then convert(int,replace(index_size, ‘KB’, ”))
    when @sortorder = 5 then convert(int,replace(unused, ‘KB’, ”))
    else Rows
    end desc

    drop table #tablesize


  6. Script doesn’t work on SQL Server 2000.

    If there’s anyone intersted here’s one that works:

    SELECT o.name AS “Table”, i.rowcnt AS “Rows”
    FROM sysobjects o, sysindexes i
    WHERE i.id = o.id
    AND indid IN(0,1)
    AND xtype = ‘u’
    AND o.name ‘sysdiagrams’
    ORDER BY i.rowcnt desc


  7. Also note – the title of the blog is named “Find Row Count in Table – Find Largest Table in Database”.

    The original result does not bring the largest table, simply the most number of rows.
    This does not necessarily mean it’s the largest table.
    The solution I posted above does however give us a way to view the results by any of the sizeable measures for an object, along with a dynamic sort for the user, thus resulting in a ‘Find Row Counts’ and ‘Find largest table’ method.



  8. How bout this?

    SELECT rows ,
    SCHEMA_NAME(t.schema_id) + ‘.’ + OBJECT_NAME(t.OBJECT_ID)
    FROM sys.tables t
    JOIN sys.partitions p ON t.object_id = p.object_id AND p.index_id IN (0,1)


  9. Ammena’s script as presented here by Pinal didn’t give me the precise row counts for table. It worked for smaller tables, but row counts were way off in larger tables. I have
    modified Ameena’s script to get the precise count as following:

    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.dm_db_partition_stats ps
    on ta.object_id = ps.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,ps.row_count
    ORDER BY sum(pa.rows) DESC


  10. We can use Object_Schema_Name()function to get the schema name of an object. So we can just use this function in your previous query to count the rows of all tables as follows.
    select OBJECT_SCHEMA_NAME(object_id)+’.’+OBJECT_NAME(object_id) as tablename,row_count
    from sys.dm_db_partition_stats
    where (index_id < 2)
    order by row_count desc;


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

  12. Thanks for the code, I’ve modified it a bit;

    SELECT QUOTENAME(SCHEMA_NAME([OBJ].[schema_id])) +’.’+ QUOTENAME([OBJ].[name]) AS [table_name]
    ,SUM([PAR].[rows]) AS [row_count]
    FROM sys.objects [OBJ]
    INNER JOIN sys.partitions [PAR]
    WHERE [OBJ].[type] = ‘U’
    AND is_ms_shipped = 0
    AND [PAR].[index_id] IN (1,0)
    GROUP BY [OBJ].[schema_id]
    ORDER BY SUM([PAR].[rows]) DESC


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

  14. Hi Pinal,
    I am facing problem in count the records in full text searching,
    select count(*) from Stock.BookDetails with(nolock) where contains(*,'”India”‘)
    it take more then 5 second.
    can you please suggest me somthing to resolve this problem..
    Thanks and regards
    Dilip Singh


  15. I found the original query actually can over-inflate the row counts on large tables. Basically there can be multiple rows returned from the sys.partitions table when the table requires additional partitions. The solution is to replace SUM(pa.rows) with SUM(pa.rows) / COUNT(pa.rows), then you’ll have the correct row count all the time.


  16. Works great, Thanks very much! Stripped the schema related criteria, since note required in my case:

    –List db user tables by size
    SELECT ta.name TableName,SUM(pa.rows) RowCnt
    FROM sys.tables ta
    INNER JOIN sys.partitions pa
    WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
    GROUP BY ta.name
    ORDER BY SUM(pa.rows) DESC


  17. Thanks Dave, great article.

    So good, that I republished it under my own name without giving you any credit. For the images, I did not even copy them, but just linked them back to your article.

    Let me know what you think of whats is now my work:[copyright violation link]



  18. Hello sir I want to ask you something.
    I have a scenario I need to maintain all the deliveries received and sent.
    I have

    Product NO
    Product Name
    Unit of measure
    Total Quantity = (Weight * Unit )
    Ref NO
    Ref Type

    Should I maintain ID , Ref No , Date , Ref Type in one table tbl_Orders
    and the remaining fields like Unit, Packing , Unit of measure , Weight and Total
    in Separate tables tbl_items.
    Or they should be in same Table

    I want to ask should I keep Deliveries Received and Deliveries Sent in the same table
    Or in Two Tables one for Received and one for Sent.

    My Basic Question is How to keep their quantities properly.
    In this application I need to Edit update record so putting quantity in separate table and add and substrate the quantity is not looking good practice here.

    Should I sum all the record of Item A to get the proper Quantity ?

    How to maintain Quantity ?

    I need to implement urgent.


  19. Hello Sir, Great Example…

    I am trying this…

    I have one table where i need the count for rows present on the said date from tbl1 and sumof records from table 2 for the same date for all employees with their name


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