This is for SQL Server.

SELECT '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']'  AS fulltable_name, SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, i.rows FROM sys.tables AS t INNER JOIN sys.sysindexes AS i ON t.object_id = i.id AND i.indid < 2 order by rows desc

Example output:

Source: http://geekswithblogs.net/TakeNote/archive/2007/09/22/115537.aspx