Getting SQL table row counts via sysindexes vs. sys.indexes
- by Bill Osuch
Among the many useful SQL snippets I regularly use is this little bit that will return row counts in a table:
SELECT so.name as TableName, MAX(si.rows) as [RowCount]
FROM sysobjects so
JOIN sysindexes si ON si.id = OBJECT_ID(so.name)
WHERE so.xtype = 'U'
GROUP BY so.name
ORDER BY [RowCount] DESC
This is handy to find tables that have grown wildly, zero-row tables that could (possibly) be dropped, or other clues into the data.
Right off the bat you may spot some "non-ideal" code - I'm using sysobjects rather than sys.objects. What's the difference? In SQL Server 2005 and later, sysobjects is no longer a table, but a "compatibility view", meant for backward compatibility only. SELECT * from each and you'll see the different data that each returns.
Microsoft advises that sysindexes could be removed in a future version of SQL Server, but this has never really been an issue for me since my company is still using SQL 2000. However, there are murmurs that we may actually migrate to 2008 some year, so I might as well go ahead and start using an updated version of this snippet on the servers that can handle it:
SELECT so.name as TableName, ddps.row_count as [RowCount]
FROM sys.objects so
JOIN sys.indexes si ON si.OBJECT_ID = so.OBJECT_ID
JOIN sys.dm_db_partition_stats AS ddps ON si.OBJECT_ID = ddps.OBJECT_ID AND si.index_id = ddps.index_id
WHERE si.index_id < 2 AND so.is_ms_shipped = 0
ORDER BY ddps.row_count DESC
