SQL Server Management Studio - Finding all non empty tables

You could try using sysindexes and INFORMATION_SCHEMA.TABLES:)

SELECT 'Table Name'=convert(char(25),t.TABLE_NAME),
      'Total Record Count'=max(i.rows)
FROM sysindexes i, INFORMATION_SCHEMA.TABLES t
WHERE t.TABLE_NAME = object_name(i.id)
      and t.TABLE_TYPE = 'BASE TABLE'
GROUP BY t.TABLE_SCHEMA, t.TABLE_NAME
HAVING max(i.rows)>0
ORDER BY 'Total Record Count' DESC

Try :

WITH TableRows AS
(
   SELECT 
      SUM(row_count) AS [RowCount], 
      OBJECT_NAME(OBJECT_ID) AS TableName
   FROM 
      sys.dm_db_partition_stats
   WHERE 
      index_id = 0 OR index_id = 1
   GROUP BY 
      OBJECT_ID
)

SELECT *
FROM TableRows
WHERE [RowCount] > 0

Morris Miao's solution uses the deprecated sys.sysindexes view; and performs the join to INFORMATION_SCHEMA.TABLES on the basis of table name, which is not guaranteed to be unique; even within a database.

Simon's solution is not scoped to the current database; but can be refined through the use of sys.tables:

SELECT r.table_name, r.row_count, r.[object_id]
FROM sys.tables t
INNER JOIN (
    SELECT OBJECT_NAME(s.[object_id]) table_name, SUM(s.row_count) row_count, s.[object_id]
    FROM sys.dm_db_partition_stats s
    WHERE s.index_id in (0,1)
    GROUP BY s.[object_id]
) r on t.[object_id] = r.[object_id]
WHERE r.row_count > 0
ORDER BY r.table_name;