SQL 2005 - Query to Find Tables with Most Rows

Question

I searched for this for a while but came up empty ... hopefully someone here can help.

Is there a query I can run on a database (SQL Server 2005) that will return the number of rows in each table?

Answer 1

You could try something like this:

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

Answer 2

Galwegian got it almost right :-) For SQL Server 2005 and up, I always recommed using the "sys.*" system views instead of the (soon to be deprecated) sysobjects and sysindexes tables.

SELECT 
    t.NAME AS 'Table Name',
    SUM(p.[Rows]) as 'Row Count'
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
WHERE 
    t.NAME NOT LIKE 'dt%' AND
    i.OBJECT_ID > 255 AND   
    i.index_id = 1
GROUP BY 
    t.NAME 
ORDER BY 
    SUM(p.[Rows]) DESC

Marc