Can I retrieve all database objects owned by a particular database?
SQL Server doesn't aggregate this type of information for you in any way, so you're going to use some kind of loop to get this information in a single resultset. One way is to concatenate a set of UNION ALL queries, but I think an explicit cursor populating a #temp table one row at a time is an easier way to digest what is happening, especially as the source query gets more complex:

First, create a #temp table:

CREATE TABLE #objs
(
  db        sysname PRIMARY KEY,
  functions int,
  procs     int,
  triggers  int,
  views     int
);


Then:

DECLARE @sql nvarchar(max) = N'SELECT DB_NAME(),
  SUM(CASE WHEN CHARINDEX(N''FUNCTION'', o.type_desc) &gt; 0 THEN 1 ELSE 0 END) [functions],
  SUM(CASE WHEN CHARINDEX(N''PROCEDURE'', o.type_desc) &gt; 0 THEN 1 ELSE 0 END) [procs],
  SUM(CASE WHEN CHARINDEX(N''TRIGGER'', o.type_desc) &gt; 0 THEN 1 ELSE 0 END) [triggers],
  SUM(CASE WHEN CHARINDEX(N''VIEW'', o.type_desc) &gt; 0 THEN 1 ELSE 0 END) [views]
FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id = o.object_id;';

DECLARE @n sysname, @e nvarchar(512);

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
  SELECT QUOTENAME(name)
  FROM sys.databases 
  WHERE database_id &gt; 4
  AND state = 0 -- online
  ORDER BY name;

OPEN c;
FETCH NEXT FROM c INTO @n;

WHILE @@FETCH_STATUS &lt;&gt; -1
BEGIN
  SET @e = @n + N'.sys.sp_executesql ';
  INSERT #objs EXEC @e @sql;
  FETCH NEXT FROM c INTO @n;
END
CLOSE c;
DEALLOCATE c;

SELECT * FROM #objs;


Note that for any database that doesn't have any of these objects you will get a row of NULL values instead of 0s. If you want to change that, just fix your pattern from:

SUM(CASE WHEN ... THEN 1 ELSE 0 END)


To:

COUNT(CASE WHEN ... THEN 1 END)


Clean up:

DROP TABLE #objs;


You can also take a look at sp_foreachdb.

For completeness, here is an example using dynamic SQL:

DECLARE @query nvarchar(max) = N'
 UNION ALL SELECT N''$dbs$'',
  SUM(CASE WHEN CHARINDEX(N''FUNCTION'', o.type_desc) &gt; 0 THEN 1 ELSE 0 END) [functions],
  SUM(CASE WHEN CHARINDEX(N''PROCEDURE'', o.type_desc) &gt; 0 THEN 1 ELSE 0 END) [procs],
  SUM(CASE WHEN CHARINDEX(N''TRIGGER'', o.type_desc) &gt; 0 THEN 1 ELSE 0 END) [triggers],
  SUM(CASE WHEN CHARINDEX(N''VIEW'', o.type_desc) &gt; 0 THEN 1 ELSE 0 END) [views]
FROM $dbv$.sys.sql_modules m INNER JOIN $dbv$.sys.objects o ON m.object_id = o.object_id';

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += REPLACE(REPLACE(@query, 
    N'$dbs$', REPLACE(name,'''','''''')),
    N'$dbv$',QUOTENAME(name))
  FROM sys.databases 
  WHERE database_id &gt; 4
  AND state = 0 -- online
  ORDER BY name;

SET @sql = STUFF(@sql, 1, 12, N'') + N';';

EXEC sys.sp_executesql @sql;