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 (https://blog.sqlauthority.com)
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.
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
Indid filter is important. Otherwise you will get duplicate rows
Thnx for the above query, I helped me a lot!! :)
Join with sys.schemas is required not to get duplicate records in the results from other schemas with same table name if exists.
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.
Here are some other methods to know the row count from the tables
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
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
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
Good inputs in terms of different approaches for finding the rowcounts in tables.
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
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
nice one..
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
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?
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;
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
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
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.
Have you tested it? sum(rows)/Count(rows) is not making sense to me.
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
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
Hi,
This is a violation of my copyright. I request you to remove it immediately from your website.
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.
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
Great. Thanks!
Thanks @Sugeesh