August 23, 2007 at 12:49 pm
I have a stored procedure in a main database that I'd like to use in several other databases. How can I copy the stored procedure using T-SQL Code or VB script?
August 23, 2007 at 1:12 pm
Did you try something like below?
EXEC master..sp_helptext sprocName
August 23, 2007 at 1:31 pm
Sorry, I don't understand what that means? My sp is not in master.
August 23, 2007 at 2:01 pm
Query analyzer might be the quickest way to do this.
Open up QA, and use the object browser to find the SP. Right-click on it, and pick "Script into new window as Create" (you have to pick the "create" from a sub-menu). Once it open up, you could simply switch the database up top to the first recipient database, then click execute, and cycle through each recipient DB until you've got it in each place you want it.
if you want a script to do all of them at once, copy the CREATE PROCEDURE code from above, and alternate that with USE <database> statements. Something like below:
Use database1
Go
<CREATE PROCEDURE CODE>
go
USE database 2
go
etc....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
August 23, 2007 at 3:12 pm
That works great for a one time copy, however, I'd like to use this when I make a change to my "main database stored procedure" I can run script to copy the sp to all dbs again as needed. Is that possible? Can I Declare a variable after the Use and input db names to copy the sp to?
August 24, 2007 at 12:47 pm
Look into doing that as a dynamic SQL statement which you would build on the fly Doing it that way - you could parameterize the USE statement to point to any DB you want.
That kind of scenario gets messy pretty quick depending on what's in the Stored proc you are trying to copy around. DTS can be used to do the same thing in a fairly straightforward manner as well.
You could always look at finding the record in the system table (in this case sysobjects) in the main database, and inserting it in the same table in the other databases. This is NOT supported, and I usually run away from that as fast as I can, but have had moments of desparation where I've needed to do such foolishness (a patch took out several key extended stored procs, like the add/delete/add security xp's, and then crashed before updating them, so we had to get creative).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply