Can I query stored procs in all databases with one query without dynamic sql
You can do it without the cursor (or without a cursor-like operation). For example you can do it slightly differently like this:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + CHAR(13) + CHAR(10)
  + N'select ''' + db.name + ''', p.name 
        from ' + QUOTENAME(db.name) + N'.sys.procedures AS p 
        WHERE p.name = N''usp_MyProc'' 
        COLLATE SQL_Latin1_General_CP1_CI_AI;' 
    FROM sys.databases AS db -- WHERE ...;

EXEC sp_executesql @sql;


You can also avoid multiple result sets going back by doing this:

CREATE TABLE #x(d SYSNAME, n SYSNAME);

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + CHAR(13) + CHAR(10)
  + N'insert #x select ''' + db.name + ''', p.name 
        FROM ' + QUOTENAME(db.name) + N'.sys.procedures AS p
        WHERE p.name = N''usp_MyProc'' 
        COLLATE SQL_Latin1_General_CP1_CI_AI;' 
    FROM sys.databases AS db -- WHERE ...;

EXEC sp_executesql @sql;

SELECT d, n FROM #x ORDER BY d, n;


If you're really only after a single, specific stored procedure though, see Martin's answer.