SQL Server 2005 – Query All Databases and Output to One Table

sql-server-2005

Need a little help with the below:

I've created a query which basically will give me all the stored procedures in all the table for every database and I want to try and amend it so that the output queries are output to one table.

Any suggestions on how to improve this?

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

Best Answer

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
Related Question