Counting rows for all tables at once

Question

I'm using SQL Server 2005 and would like to know how I can get a list of all tables with the number of records in each.

I know I can get a list of tables using the sys.tables view, but I'm unable to find the count.

Thank you

Answer 1

From here: http://web.archive.org/web/20080701045806/http://sqlserver2000.databases.aspfaq.com:80/how-do-i-get-a-list-of-sql-server-tables-and-their-row-counts.html

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

For what it's worth, the sysindexes system table is deprecated in SQL 2008. The above still works, but here's query that works going forward with SQL 2008 system views.

select
schema_name(obj.schema_id) + '.' + obj.name,
row_count
from (
    select
        object_id,
        row_count = sum(row_count)
    from sys.dm_db_partition_stats
    where index_id < 2  -- heap or clustered index
    group by object_id
) Q
join sys.tables obj on obj.object_id = Q.object_id