SQL 2005 query all databases and output to one table
select  *,DB_NAME() as DB_NAME
into    mytable
from    sys.all_objects
where   1=2




DECLARE @command varchar(1000) 
SELECT @command = 

'USE [?] select *,  DB_NAME() as DatabaseName  
from sys.all_objects where type=''p'' and is_ms_shipped=0 '

insert into mytable  EXEC sp_MSforeachdb @command 




Using table variable

declare  @mytable table (
    [name] [sysname] NOT NULL,
    [object_id] [int] NOT NULL,
    [principal_id] [int] NULL,
    [schema_id] [int] NOT NULL,
    [parent_object_id] [int] NOT NULL,
    [type] [char](2) NULL,
    [type_desc] [nvarchar](60) NULL,
    [create_date] [datetime] NOT NULL,
    [modify_date] [datetime] NOT NULL,
    [is_ms_shipped] [bit] NULL,
    [is_published] [bit] NULL,
    [is_schema_published] [bit] NULL,
    [DB_NAME] [nvarchar](128) NULL
    )


DECLARE @command varchar(1000) 
SELECT @command = 

'USE [?] select *,  DB_NAME() as DatabaseName  
from sys.all_objects where type=''p'' and is_ms_shipped=0 '

insert into @mytable  EXEC sp_MSforeachdb @command 

select * from @mytable