This query returns a list of tables in a database with their number of rows.

Query

select schema_name(tab.schema_id) + '.' + tab.name as [table],
       sum(part.rows) as [rows]
   from sys.tables as tab
        inner join sys.partitions as part
            on tab.object_id = part.object_id
where part.index_id IN (1, 0) -- 0 - table without PK, 1 table with PK
group by schema_name(tab.schema_id) + '.' + tab.name
order by sum(part.rows) desc

Columns

• table - table name with schema name
• rows - number of rows in a table

Rows

• One row: represents one table
• Scope of rows: all tables in a database including tables without rows
• Ordered by: number of rows in descending order, from largest to smallest (in terms of number of rows)

Sample results

Tables by number of rows in the AdventureWorksLT database: