views:

254

answers:

3

Is there a way in SQL Server 2008 to find the table with the most rows in the database?

+5  A: 

This will get you close:

SELECT 
    [TableName] = so.name, 
    [RowCount] = MAX(si.rows) 
FROM 
    sysobjects so, 
    sysindexes si 
WHERE 
    so.xtype = 'U' 
    AND 
    si.id = OBJECT_ID(so.name) 
GROUP BY 
    so.name 
ORDER BY 
    2 DESC
Chris Ballance
Works great. Thank you.
mr_dunski
Looks very good! Will it work with tables that don't have any index (or a primary key, which becomes automatically an index) ?
MaxiWheat
I don't see why it wouldn't work with tables without an explicit index.
Chris Ballance
Note that sysindexes is a compatibility view and might not be available in some future edition. Also, the actual number of rows might not match sysindexes.rows, because sysindexes.rows is not updated for every modification to the table. If you need exact row counts, you have to use COUNT(*) and access the actual table. Finally, in SQL Server 2008, there are filtered indexes, so the number of rows in an index may be smaller than in the table it indexes. Since you're looking for the MAX, it's not a problem, but it means this may not generalize in the obvious way.
Steve Kass
+1 @Steve Kass thanks for the due diligence regarding edge cases for the query I posted!
Chris Ballance
A: 

See here It returns, most rows, table size etc...

Rippo
A: 

I just customize my SSMS 2008 to show the following additional columns for tables - Row Count - Data Space Used (KB)

for databases - Primary Data Location - Last Backup Date - Created Date ....

Works quicker for me most of the time without opening a query, I just click on the column header to go ASC or DESC

jerryhung