Use store procedure SP_MSFOREACHTABLE

sp_msforeachtable 'select "?", count(*) from ?'

Result is a row count for each table in a database.

Result example:

[dbo].[TCO_DBSpace] 0

---------------------- -----------

[dbo].[TCO88_DB_Space] 986

If your statistics in your database is up to date, you may use:

Select a.name, b.rows from sysobjects a, sysindexes b

Where a.id = b.id and b.indid <= 1 and a.type = 'U'

And a.name <> 'dtproperties'

Order by a.name

Result example:

TCO_DBSpace 0

TCO88_DB_Space 986