FIND ROWCOUNT FOR ALL TABLES

Need to find out which of your tables have the highest rowcounts? Pinal Dave submitted a solution on his blog (sqlauthority.com). I’ve modified it to include only user tables:

USE Northwind
GO
SELECT OBJECT_NAME(st.object_id) TableName, st.row_count
FROM sys.dm_db_partition_stats st
join sys.objects ob
on ob.object_id = st.object_id
WHERE st.index_id < 2
and ob.type = ‘U’
ORDER BY st.row_count DESC>

The above script works for SQL Server 2005. For SQL Server 2000, Afonso Mata posted compatible code on Pinal’s blog that I’ve also drawn from. Try:

USE Northwind
GO
SELECT OBJECT_NAME(si.id) TableName, si.rowcnt
FROM sysindexes si
join sysobjects ob
on ob.id = si.id
WHERE OBJECTPROPERTY(si.id,’isUserTable’)=1 AND si.indid < 2
and ob.type = ‘U’
ORDER BY si.rowcnt DESC

http://blog.sqlauthority.com/2009/01/13/sql-server-find-row-count-in-table-find-largest-table-in-database-t-sql/#comment-62037