How to copy stored procedures and user functions between databases???
-
Please can someone advise? I am using SQL 2005 Express and want to copy the stored procedures and user functions (not the tables) from one database to another on the same instance or on another system. Since I will be doing this several times, I would like to write a sp to do it.
thank you in advance!
John B
-
Hello,
If I understand your question correctly, all you need do is to go into Management Studio, right click on the database that contains the stored procedures and user functions that you wish to copy, and click on Tasks >> Generate Scripts. This will open up the scripting wizard. In this wizard, specify the source database, and on the next screen, specify any options that you want. On the next screen, you will be presented with a "Choose Object Types" screen that allows you to specify what types of objects you want to script. Select (for your purposes) Stored Procedures & User-defined functions. Then, choose which procedures you want (or just click the Select All button) and which functions you want (or just click the Select All button). You can choose to save the resulting script to a file, copy it to the clipboard, or present it in a new query window. At this point, you can save the generated code in a .sql file in order to run it in the target database. Alternatively, I'm not familiar with the intricacies of SSIS, but if you're running it, I'm sure there's a way you can save the code in what is the equivalent of a SS2K DTS package in order to have the code stored in the server and available for reuse. Hope this helps!
VL
-
VL, it does indeed help! Even the free SSME tool includes a generate scripts function, which I had totally overlooked. Thank you very much!
John B
-
Thanks a million. I have hundreds of SPs to copy and I thought I was going to have to do them all individual. You saved me hours. Take the rest of the week off.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply