SELECT
  o.name AS "Table Name",
  SUM(i.rowcnt) AS "Row Count"
FROM sysobjects o
  INNER JOIN sysindexes i
    ON o.id = i.id
WHERE
  i.indid IN (0, 1)
  AND o.xtype = 'u'
  AND o.name <> 'sysdiagrams'
GROUP BY
  o.name
ORDER BY
  "Row Count" DESC;

SELECT 
  OBJECT_NAME(object_id) AS "Table Name", 
  SUM(row_count) AS "Row Count"
FROM sys.dm_db_partition_stats 
WHERE 
  index_id < 2 
  AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0 
GROUP BY 
  object_id
ORDER BY 
  "Row Count" DESC;

DECLARE @tblROWCOUNT TABLE 
(
"Db name" VARCHAR(1000),
"Table Name" VARCHAR(400),
"Row Count" BIGINT	
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = 'SELECT ''?'' ,o.name, i.rowcnt  FROM ?.sys.sysobjects o, ?.sys.sysindexes'
			  +' i WHERE i.id = o.id AND indid IN(0,1) AND xtype = ''u'''
			  +	'AND o.name <> ''sysdiagrams'' AND i.rowcnt > 0  ORDER BY i.rowcnt DESC'

INSERT INTO @tblROWCOUNT

EXEC Sp_msforeachdb   @SQL

select * from @tblROWCOUNT