如何将存储过程脚本化到文件中? [英] How to script out stored procedures to files?

问题描述

有没有办法可以找到存储过程的保存位置，以便我可以将文件复制到桌面?

Is there a way that I can find where stored procedures are saved so that I can just copy the files to my desktop?

推荐答案

存储过程不存储为文件，它们存储为元数据并向我们公开(感谢 Michael 提醒关于​​ sysschobjs) 在目录视图 sys.objects, sys.procedures, sys.sql_modules 等.对于单个存储过程，可以直接使用这些视图查询定义(最重要的是sys.sql_modules.definition) 或使用 OBJECT_DEFINITION() 函数为 Nicholas 指出(尽管他对 syscomments 并不完全准确).

Stored procedures aren't stored as files, they're stored as metadata and exposed to us peons (thanks Michael for the reminder about sysschobjs) in the catalog views sys.objects, sys.procedures, sys.sql_modules, etc. For an individual stored procedure, you can query the definition directly using these views (most importantly sys.sql_modules.definition) or using the OBJECT_DEFINITION() function as Nicholas pointed out (though his description of syscomments is not entirely accurate).

要将所有存储过程提取到单个文件中，一种选择是打开对象资源管理器，展开 您的服务器 >数据库 >你的数据库 >可编程性 并突出显示stored processes 节点.然后点击 F7(查看 > 对象资源管理器详细信息).在右侧，选择您想要的所有过程，然后右键单击，script stored procedure as >创建到 >文件.这将生成一个包含您选择的所有程序的文件.如果您希望每个过程有一个文件，您可以通过一次只选择一个过程来使用此方法，但这可能很乏味.您还可以使用此方法将所有与会计相关的程序编写到一个文件中，将所有与财务相关的程序编写到另一个文件中，等等.

To extract all stored procedures to a single file, one option would be to open Object Explorer, expand your server > databases > your database > programmability and highlight the stored procedures node. Then hit F7 (View > Object Explorer Details). On the right-hand side, select all of the procedures you want, then right-click, script stored procedure as > create to > file. This will produce a single file with all of the procedures you've selected. If you want a single file for each procedure, you could use this method by only selecting one procedure at a time, but that could be tedious. You could also use this method to script all accounting-related procedures to one file, all finance-related procedures to another file, etc.

为每个存储过程生成一个文件的更简单方法是使用 生成脚本向导 - 再次从对象资源管理器开始 - 右键单击​​您的数据库并选择 Tasks >生成脚本.选择 Select specific database objects 并选中顶级 Stored Procedures 框.点击下一步.对于输出，选择 Save scripts to a specific location、Save to file 和 单个文件每个对象.

An easier way to generate exactly one file per stored procedure would be to use the Generate Scripts wizard - again, starting from Object Explorer - right-click your database and choose Tasks > Generate scripts. Choose Select specific database objects and check the top-level Stored Procedures box. Click Next. For output choose Save scripts to a specific location, Save to file, and Single file per object.

这些步骤可能略有不同，具体取决于您的 SSMS 版本.

这篇关于如何将存储过程脚本化到文件中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！