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

Above query will return following resultset.

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

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 (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – Index Levels and Delete Operations – Page Level Observation
Next Post
SQL SERVER – Quickly Upgrade Your SQL Server

Related Posts

30 Comments. Leave new

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

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

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

    Reply
  • Here are some other methods to know the row count from the tables

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

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

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

    Reply
  • Good inputs in terms of different approaches for finding the rowcounts in tables.

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

    :)
    Imtiaz

    Reply
  • 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)
    ORDER BY 1 DESC

    Reply
  • 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
    ,ps.row_count
    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

    Reply
    • Paresh Prajapati
      March 29, 2017 12:36 pm

      Subhash,

      dm_db_partition_stats can have multiple rows for same table. Try adding “AND ps.index_id IN (1,0) ” as below.

      It gives no difference in my database.

      select pa.object_id,ta.name
      ,sum(pa.rows) as pa_rows
      ,ps.row_count 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) AND ps.index_id IN (1,0)
      group by pa.object_id,ta.name,ps.row_count
      having sum(pa.rows) != ps.row_count

      But still have one question…. On which table we can rely to get accurate row counts?
      sys.partitions / sys.sysindexes / sys.dm_db_partition_stats / any other table or method?

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

    Reply
  • 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]
    ON [PAR].OBJECT_ID = [OBJ].OBJECT_ID
    WHERE [OBJ].[type] = ‘U’
    AND is_ms_shipped = 0
    AND [PAR].[index_id] IN (1,0)
    GROUP BY [OBJ].[schema_id]
    ,[OBJ].[name]
    ORDER BY SUM([PAR].[rows]) DESC

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

    Reply
  • cuddlykittens
    March 4, 2015 4:59 am

    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.

    Reply
  • Leon Kuperman
    March 18, 2015 8:46 pm

    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
    ON pa.OBJECT_ID = ta.OBJECT_ID
    WHERE ta.is_ms_shipped = 0 AND pa.index_id IN (1,0)
    GROUP BY ta.name
    ORDER BY SUM(pa.rows) DESC

    Reply
  • Mohamed M Abdelfatah
    April 19, 2015 7:51 am

    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]

    Mohamed

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

    ID
    Product NO
    Product Name
    Unit
    Packing
    Unit of measure
    Weight
    Total Quantity = (Weight * Unit )
    Ref NO
    Date
    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.

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

    Reply
  • Sugeesh Ariyaratna
    May 26, 2016 12:34 pm

    Great. Thanks!

    Reply

Leave a Reply