[b]COPYING STORED PROCEDURES[/b]

[kissarmi]

Hi,

I use a lot of Stored Procedures with my Apps. I also use several databases at the same time. The databases represent fiscal years of data, so the only difference between them is updates made to the information from year to year. When a new year comes around, I simply copy the tables from the previous year into the new year database. Is there any way to simply copy all the stored procedures from one database to another without having to individually copy and paste the SP code?

Thanks for any help.

 

[checkai]

You can highlight them all and Generate a Create/Delete Script which you can then run on the other database to create all of the sp's at once...

DLC

 

[vongrunt]

You can generate single SQL script for all stored procedures at once w/ enterprise manager (DB -> all tasks -> generate SQL Script -> Show All -> all stored procedures), save SQL file and use it when necessary.

 

[donutman]

Why not just use EM and import all of the SPs. It just requires a clicking a checkbox on all the SPs. Just use the import wizzard and uncheck "copy all objects". However, a script is faster the next time you need it.
-Karl

 

[snag56]

Using Enterprise Manager, I would use a DTS package for this, but it will still need to be tweaked each time to point to the new target database.