SQL Server Row Count for All Tables

Option 1

 

SELECT

    sysobjects.Name, sysindexes.Rows

FROM

    sysobjects

    INNER JOIN sysindexes

    ON sysobjects.id = sysindexes.id

WHERE

    type = 'U'

    AND sysindexes.IndId < 2

ORDER BY

sysobjects.Name

 

Option 2

 

SELECT OBJECT_NAME(OBJECT_ID) TableName, st.row_count

FROM sys.dm_db_partition_stats st

WHERE index_id < 2

ORDER BY st.row_count DESC

 

Reference

http://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/