Copying a stored procedure to multiple databases

[DanC]

I'm trying to copy a stored procedure to several client databases using the sp_MSforeachdb but am having some trouble.

the command i'm trying to run is:

EXEC sp_MSforeachdb @command1 = 'Use ? ; if not exists (select [name] from dbo.sysobjects where id = object_id(N'[dbo].[mystoredprocedure]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
create procedure mystoredprocedure as
select * from mytable

or something like that. I keep getting an incorrect syntax near create error. Can anyone tell me what I'm doing wrong?

 

[cheyney]

try this:

EXEC sp_MSforeachdb @command1 = 'Use ? if not exists (select [name] from dbo.sysobjects where id = object_id(N''[dbo].[mystoredprocedure]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)
BEGIN
declare @temp varchar(500)
select @temp = ''create procedure mystoredprocedure as
select * from mytable''
exec (@temp)
END
'


cheyney

 

[DanC]

thanks a lot, that solved the problem