SQL: programmatically copy stored procedures from one db to another

Question

I'm looking for a way to copy stored procedures from one sql database to another on the same instance. It needs to be automatic, preferably through code (t-sql or anything), so using the generate scripts trick is not viable (plus I don't want to maintain that many scripts, and people forget to run them).

I've searched a bit on this and have not found a workable solution. Someone suggested a clever trick with generating all the stored procedure text into a sql field and then converting that and executing it on the destination db but unfortunately that had issues.

Has anyone got any other ideas on how this can be done, if it's at all possible?

If I can't do it programmatically, would there be a quick solution using ssis?

Thanks.

Edit: Using mixture of sql 2005 and 2008 versions.

What have you found? Don't your have the procedure sources / could you not use them directly? Why did the clever trick you mention not work? — Denis de Bernardy
– Denis de Bernardy, Commented May 25, 2011 at 11:56
I haven't tried RedGate's SQL Tools, im trying to keep it native as this is the final part of a database comparison tool I'm writing. It has issues with comments I think and wouldn't execute. — Richy321
– Richy321, Commented May 25, 2011 at 12:02
It would also be helpful to know what version of SQL Server you are using. — JNK
– JNK, Commented May 25, 2011 at 12:37

JNK · Accepted Answer · 2011-05-25 12:32:30Z

1

You can do it programatically in .NET using the SMO framework.

Free/easy implementation could be done via PowerShell.

I have a script that does just this - it generates the scripts for SQL objects, including Stored Procs, and executes the creation scripts on the target server.

It's a handy workaround when security concerns don't allow linked servers but you need to keep certain resources in sync across multiple servers.

If all you care about are the procs, it should be fairly straightforward to check sys.sql_modules on your source and target DBs and execute any that don't exist in the target via the definition field in that view.

answered May 25, 2011 at 12:32

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL: programmatically copy stored procedures from one db to another

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related