SQL:以编程方式将存储过程从一个数据库复制到另一个数据库 [英] SQL: programmatically copy stored procedures from one db to another

问题描述

我正在寻找一种将存储过程从一个 sql 数据库复制到同一实例上的另一个的方法.它需要是自动的，最好是通过代码(t-sql 或任何东西)，所以使用生成脚本技巧是不可行的(而且我不想维护那么多脚本，人们忘记运行它们).

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

我对此进行了一些搜索，但没有找到可行的解决方案.有人提出了一个聪明的技巧，将所有存储过程文本生成到一个 sql 字段中，然后将其转换并在目标数据库上执行，但不幸的是，这出现了问题.

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?

如果我不能以编程方式完成，是否有使用 ssis 的快速解决方案?

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

谢谢.

混合使用 sql 2005 和 2008 版本.

Using mixture of sql 2005 and 2008 versions.

推荐答案

您可以使用 SMO 框架.

可以通过 PowerShell 来免费/轻松实现.

我有一个脚本可以做到这一点 - 它为 SQL 对象生成脚本，包括存储过程，并在目标服务器上执行创建脚本.

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.

如果您只关心过程，那么检查源和目标数据库上的 sys.sql_modules 并通过 definition 该视图中的字段.

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.

这篇关于SQL:以编程方式将存储过程从一个数据库复制到另一个数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！