c# - SM？使用 if exists drop then create 和 no sp_executesql 编写所有 SQL 存储过程的脚本

Question

我有一个看似简单的要求。我需要按照此标准编写数据库中所有存储过程的脚本。

1. 如果过程存在，脚本需要包含删除，如下所示...

   IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[myproc]') AND type in (N'P', N'PC'))
   

2. 脚本不能有 sp_executesql 所以没有这样的...

   EXEC dbo.sp_executesql @statement = '....'
   

3. 我需要为每个存储过程创建一个单独的脚本文件。所以它将是 [存储过程名称].sql

我注意到，当我尝试内置的 sql 生成脚本时，我可以通过复选框在单独的文件中获取 procs，以便在单独的文件中编写脚本对象，我还可以获得 if exists drop。但是，它使用了他们不想要的 sp_executesql。

所以我花了一点时间尝试 SMO 并发现了类似的问题......

一个。遗憾的是，以下只是编写了 drop 语句的脚本。我无法将它与创建结合起来。所以我可以获得单独的文件并且没有 sp_executesql 但我仍然缺少上面的 #1

    Scripter scripter = new Scripter();
    scripter.Options.ScriptDrops = true;

B.其次，以下选项将输出更改为使用 sp_executesql

    scripter.Options.IncludeIfNotExists = true;

C.最后，我可以手动添加文本。它成功地设置在 TextHeader 中。但是，scripter.Script() 抛出异常“{”StoredProcedure 'dbo.myproc' 的脚本失败。 "

    storedProcedure.TextHeader = string.Format("IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'{0}') AND type in (N'P', N'PC')) \r\nDROP PROCEDURE {0} \r\nGO\r\n{1}", storedProcedure.Name, storedProcedure.TextHeader);
    scripter.Options.FileName = Path.Combine(storedProceduresPath, storedProcedure.Name + ".sql");
    scripter.Script(new Urn[] { storedProcedure.Urn });  //Exception! - Script failed for StoredProcedure

我无法想象这是一件多么奇怪的事情，所以我想知道人们是如何做到这一点的？？？如果需要使用 sql - 任务 - 生成脚本创建单独的文件，然后是一个应用程序来清除不需要的“EXEC dbo.sp_executesql @statement = N'”

Answer 1

以下代码示例在 PowerShell 中，但使用 SMO，因此您可以轻松地将其转换为 C#。

Scripter 感觉有点慢，所以我使用了一个稍微不正统的方法，但这对我来说已经成功了数百次。

$sp 是您数据库中的一个存储过程，仅用于遍历所有 SP:

foreach($sp in $db.StoredProcedures)

脚本删除:只需使用 String.Format :)

$sbHead.AppendLine( [string]::Format(@"
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[{0}].[{1}]') AND type in (N'P', N'PC'))
GO
"@, $sp.Schema, $sp.Name))

脚本正文

$sbBody.AppendLine( [string]::Format(@"
print 'creating "{1}"...'
--   *  *  *  *  #{0}:{1}; CreateDate:{2}, DateLastModified:{3}   *  *  *  *
GO
{4}
{5}
GO
"@, $cnt, $sp.Name, $sp.CreateDate, $sp.DateLastModified, $sp.TextHeader, $sp.TextBody))

这会打印出一些额外的信息:

• print 'creating "{1}"...' 把它放在那里因为有一次创作只是挂了几分钟。知道当前的 SP 名称引导我们问题的根源:与链接服务器的连接是下来……
• SP 创建和最后修改日期，但这不是真正需要的。

此方法不考虑脚本依赖性，因此您将在控制台中收到一条警告消息，如“当前的 SP xy 依赖于 SP dsa，但无论如何都要创建它”。但是由于没有解决这些依赖关系，运行速度更快。一直为我工作...

已将完整脚本和示例输出上传到 GitHub:SqlScriptExport.ps1

这个做了一些额外的事情:

• 仅编写以给定前缀(或全部，如果前缀为空)开头的 SP
• 还有脚本 View ，相同的前缀过滤。