SQL 2005 查詢所有數據庫並輸出到一張表

在以下方面需要一點幫助：

我創建了一個查詢，它基本上將為我提供每個數據庫的所有表中的所有儲存過程，我想嘗試修改它，以便輸出查詢輸出到一個表。

關於如何改進這一點的任何建議？

DECLARE @command varchar(1000) 
SELECT @command = 

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

EXEC sp_MSforeachdb @command

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 

---

使用表變數

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

引用自：https://dba.stackexchange.com/questions/158032