Here's a code to show you how can it be done. You might need to fix it.

DECLARE @dbname sysname

CREATE TABLE #counts( totalcounts int)

DECLARE DBs CURSOR FOR

SELECT name

FROM sys.databases

WHERE database_id > 4

AND state = 0

OPEN DBs

FETCH NEXT FROM DBs INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

EXECUTE( 'INSERT INTO #counts SELECT COUNT(*) FROM [' + @dbname + '].sys.tables' )

FETCH NEXT FROM DBs INTO @dbname

END

CLOSE DBs

DEALLOCATE DBs

SELECT SUM(totalcounts)

FROM #counts

DROP TABLE #counts