sql server - 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.

Answer 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.