sql - Running a Stored Procedure on All Databases, and Saving All Results Into A Table -

sql - Running a Stored Procedure on All Databases, and Saving All Results Into A Table -

i'm trying create query in cycle through databases in server, run stored procedure on it, , save results table.

this have far:

create table results (severity int, purchaseordernumber int, purchaseorderlinenumber smallint, shipmentnumber smallint, errorcode int, errortext varchar(256), datecreated datetime) execute sp_msforeachdb ' if "?" "vis%" begin utilize "?" insert results execute ic_purchaseorder end ' select * results drop table results

what i'm hoping accomplish code run ic_purchaseorder stored procedure on of databases in server, , record result of created results table. after that, able e-mail results off supervisor, , drop table, that's job day. know there exists syntax error in if statement, results in next error

msg 102, level 15, state 1, line 4 wrong syntax near '{insert database name here}'.

would possible insight i'm trying accomplish? thanks!

update code follows, should trick:

create table results (severity int, purchaseordernumber int, purchaseorderlinenumber smallint, shipmentnumber smallint, errorcode int, errortext varchar(256), datecreated datetime) exec sp_msforeachdb 'use [?]; if ''?'' ''vis%'' begin if object_id(''ic_purchaseorder'') not null begin insert results execute ic_purchaseorder end else begin print ''missing proc in ?'' end end' select * results drop table results

simple test... run this, tell db's '%a%':

exec sp_msforeachdb 'use [?]; if ''?'' ''%a%'' begin select ''? a'' end else begin select ''? not a'' end'

sql sql-server stored-procedures sp-msforeachdb