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
2009-10-01 18:29:35
Works great. Thank you.
mr_dunski
2009-10-01 18:41:33
Looks very good! Will it work with tables that don't have any index (or a primary key, which becomes automatically an index) ?
MaxiWheat
2009-10-01 18:43:18
I don't see why it wouldn't work with tables without an explicit index.
Chris Ballance
2009-10-01 18:50:34
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
2009-10-01 20:49:58
+1 @Steve Kass thanks for the due diligence regarding edge cases for the query I posted!
Chris Ballance
2009-11-26 13:09:49
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
2009-10-01 19:40:29