导出“功能"许多存储过程编写脚本 [英] Export the "functionality" of many stored procedures to script

问题描述

我有大量的存储过程(200 多个)，它们都收集临床数据并将结果插入到一个公共表中.每个存储过程都接受相同的单个参数 ClientID，然后编译诊断结果列表并将它们插入到主表中.

I have a large number of stored procedures (200+) that all collect clinical data and insert the result into a common table. Each stored procedure accepts the same single parameter, ClientID, and then compiles a list of diagnostic results and inserts them into a master table.

我将每个临床测试分成单独的存储过程，但是正如我在 previous SO question，这些存储过程的批处理的执行将 CPU 固定在 100% 并持续数小时，然后最终失败.这使我想要创建一个包含存储过程所有功能的脚本.你为什么问?嗯，因为它有效.我更愿意将逻辑保留在存储过程中，但在我弄清楚为什么存储过程如此缓慢和失败之前，我需要继续使用脚本"方法.

I have each clinical test separated into individual stored procedures however as I described in a previous SO question, the execution of the batch of these stored procedures pegs the CPU at 100% and continues on for hours before eventually failing. This leads me to want to create a single script that contains all the functionality of the stored procedures. Why you ask? Well, because it works. I would prefer to keep the logic in the stored procedure but until I can figure out why the stored procedures are so slow, and failing, I need to proceed with the "script" method.

所以，我想要做的是获取所有存储过程并找到一种方法将它们的功能脚本"到单个 SQL 脚本中.我可以使用任务 => 生成脚本"向导，但结果包含我不知道的所有 Create Procedure 和 Begin 和 End 功能不需要.

So, what I am looking to do is to take all the stored procedures and find a way to "script" their functionality out to a single SQL script. I can use the "Tasks => Generate Scripts" wizard but the result contains all the Create Procedure and Begin and End functionality that I don't need.

推荐答案

在我使用的studio等版本中，有选项可以控制是否编写if exists statements".

In the versions of studio, etc. I use, there are options to control whether to script out the "if exists statements".

如果您只想在没有 create 语句的情况下捕获 procs，您可以使用 sp_helptext proc 轻松地滚动您自己的程序

If you just want to capture the procs without the create statements, you could be able to roll your own pretty easily usig sp_helptext proc

例如，我创建了这个过程

For example, I created this proc

create proc dummy (
@var1 int
, @var2 varchar(10)
) as
begin
return 0
end

当我运行 sp_helptext dummy 时，我得到的结果与输出几乎完全相同.评论也将被包括

When I ran sp_helptext dummy I get pretty much the exact same thing as the output. Comments would also be included

我不知道有任何工具会在没有 create 的情况下返回内容"，因为形式参数是 create 或 alter 语句的一部分.这可能会让您使用 perl、python 或任何方法来复制 create 语句——您丢失了参数——尽管我想您可以将它们更改为注释.

I don't know of any tool that is going to return the "contents" without the create, as the formal parameters are part of the create or alter statement. Which probably leaves you using perl, python, whatever to copy out the create statement -- you lose the parameters -- though I suppose you could change those into comments.

这篇关于导出“功能"许多存储过程编写脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！