20

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

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
mr_dunski
  • 441
  • 1
  • 3
  • 10

3 Answers3

49

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
  • 33,810
  • 26
  • 104
  • 151
  • Looks very good! Will it work with tables that don't have any index (or a primary key, which becomes automatically an index) ? – MaxiWheat Oct 01 '09 at 18:43
  • I don't see why it wouldn't work with tables without an explicit index. – Chris Ballance Oct 01 '09 at 18:50
  • 3
    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 Oct 01 '09 at 20:49
  • +1 @Steve Kass thanks for the due diligence regarding edge cases for the query I posted! – Chris Ballance Nov 26 '09 at 13:09
12

Here's basically the same T-SQL that Chris Ballance provided, but using the new Object Catalog Views instead of the compatability views:

SELECT  SchemaName = schemas.[name],
        TableName = tables.[name],
        IndexName = indexes.[name],
        IndexType =
            CASE indexes.type
                WHEN 0 THEN 'Heap'
                WHEN 1 THEN 'Clustered'
            END,
        IndexPartitionCount = partition_info.PartitionCount,
        IndexTotalRows = partition_info.TotalRows
FROM    sys.tables
        JOIN sys.indexes
            ON  tables.object_id = indexes.object_id
                AND indexes.type IN ( 0, 1 )
        JOIN (  SELECT object_id, index_id, PartitionCount = COUNT(*), TotalRows = SUM(rows)
                FROM sys.partitions
                GROUP BY object_id, index_id
        ) partition_info
            ON  indexes.object_id = partition_info.object_id
                AND indexes.index_id = partition_info.index_id
        JOIN sys.schemas ON tables.schema_id = schemas.schema_id
ORDER BY SchemaName, TableName;
Kenny Evitt
  • 9,291
  • 5
  • 65
  • 93
  • 1
    The formatting off of this is amazing, we have a complex eCommerce database with multiple schemas and the visibility and sorting options here is excellent. My only change is to add 'IndexTotalRows DESC' as the first option in the 'ORDER BY' so I can see where my tables are getting bloated by my code, intentionally or otherwise – James Gray Jan 14 '15 at 20:48
  • 1
    Thanks for adding a version based on Object Catalog, Kenny. – Chris Ballance Aug 17 '16 at 12:59
  • 1
    I was having trouble with the accepted answer with tables in different schemas. This solution seemed to work well in those cases. – Adam Plocher Nov 18 '20 at 06:36
1

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
  • 1,043
  • 6
  • 10