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.

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)

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

    Like

  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

    Like

  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.

    Like

  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

    Like

  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

    Like

  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

    Like

  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.

    :)
    Imtiaz

    Like

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

    Like

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

    Like

  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;

    Like

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

    Like

  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

    Like

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