Create or Alter a stored procedure
-
Is there a way to Create or Alter a stored procedure in multiple database at one time. I have several database on my server. I only want to create or alter a stored procedure on databases that contain a table called AccountSum. Can anyone tell how to accomplish this?
I intend to use this process only once. Its just that I have to may databases on my server.
-
This isn't going to be what you want to hear, but personally, I recommend against processes that create or alter procs affecting multiple databases. If you create this process, then you and all future DBAs will need to know never to create a table in any database named "AccountSum" that doesn't exactly mimic the one you intended the process to be for. My preference would be to write the create or alter proc script, and then execute it on the necessary databases. I realize this would take more effort, but it is more safe.
-
I agree with Grasshopper, however if you truely need to do this consider the "undocumented sp_MSForeachDB and sp_MSForeachTable" stored procedures.
Here is some sample code: (this may be more bother than it's worth because you have to convert your SP to a large string. You must include the USE ? near the top of the important command otherwise it won't work.)
sp_MSforeachdb @command1 = 'Print ''The name of the current database is ?''',
@command2 =
'begin
use ?
if exists (select 1
from sysobjects
where id = object_id(''AccountSum'')
and type = ''U'')
begin
if exists (select 1
from sysobjects
where id = object_id(''uspTest1'')
and type = ''P'')
begin
drop procedure uspTest1
print ''Dropped existing SP''
end
declare @sqlstr nvarchar(500)
set @sqlstr = ''create procedure uspTest1 as
begin
print ''''testing..''''
end
''
exec sp_executesql @sqlstr
print ''? created SP''
end
else
print ''? not updated''
end '
HTH
-
Aw heck... let's continue the corruption to the max... give it an sp_ prefix and drop it into Master
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply