将 exec sp_executesql 转换为普通查询的简单方法？

Question

在使用 Profiler 和 SSMS 处理调试查询时，我很常见的是从 Profiler 复制查询并在 SSMS 中测试它们。 因为我使用参数化 sql，所以我的查询全部作为 exec sp_executesql 查询发送。

exec sp_executesql 
N'/*some query here*/', 
N'@someParameter tinyint',
@ someParameter =2

我会将其转换为普通查询，以便于编辑（智能感知、错误检查、行号等）：

DECLARE @someParameter tinyint
SET @someParameter = 2

/*some query here*/

当然，查询越大、越复杂，就越难做到这一点。 当您多次来回时，这可能会很麻烦并且会占用大量时间。

有没有一种简单的（例如宏命令）方法可以将 muhexecutesql 转换为更方便的东西？

Answer 1

我花了一点时间制作了一个简单的脚本来为我完成这个任务。 这是一个 WIP，但我在它前面贴了一个（非常丑陋的）网页，如果你想尝试一下，它现在托管在这里：

http://execsqlformat.herokuapp.com/

示例输入：

exec sp_executesql 
          N'SELECT * FROM AdventureWorks.HumanResources.Employee 
          WHERE ManagerID = @level',
          N'@level tinyint',
          @level = 109;

和输出：

BEGIN
DECLARE @level tinyint;

SET @level = 109;

SELECT * FROM AdventureWorks.HumanResources.Employee  
          WHERE ManagerID = @level
END

一旦我从输入中提取实际 SQL 语句的格式，就可以使用 http://sqlformat.appspot.com

Answer 2

我花了一点时间，对 Matt Roberts / Wangzq 解决方案做了一个小修改，没有 DECLAREs 部分，你可以在 .NET Fiddle< /a> 或下载 LINQPad 5 文件。

输入：

exec sp_executesql N'UPDATE MyTable SET [Field1] = @0, [Field2] = @1',N'@0 nvarchar(max) ,@1 int',@0=N'String',@1=0

输出：

UPDATE MyTable SET [Field1] = N'String', [Field2] = 0

代码：

using System;
using System.Linq;
using System.Text.RegularExpressions;

public class Program
{
    public static void Main()
    {
        var sql = @"exec sp_executesql N'UPDATE MyTable SET [Field1] = @0, [Field2] = @1',N'@0 nvarchar(max) ,@1 int',@0=N'String',@1=0";
        Console.WriteLine(ConvertSql(sql));
    }

    public static string ConvertSql(string origSql)
    {
        var re = new Regex(@"exec*\s*sp_executesql\s+N'([\s\S]*)',\s*N'(@[\s\S]*?)',\s*([\s\S]*)", RegexOptions.IgnoreCase); // 1: the sql, 2: the declare, 3: the setting
        var match = re.Match(origSql);
        if (match.Success)
        {
            var sql = match.Groups[1].Value.Replace("''", "'");
            //var declare = match.Groups[2].Value;
            var setting = match.Groups[3].Value + ',';

            // to deal with comma or single quote in variable values, we can use the variable name to split
            var re2 = new Regex(@"@[^',]*?\s*=");
            var variables = re2.Matches(setting).Cast<Match>().Select(m => m.Value).ToArray();
            var values = re2.Split(setting).Where(s=>!string.IsNullOrWhiteSpace(s)).Select(m => m.Trim(',').Trim().Trim(';')).ToArray();

            for (int i = variables.Length-1; i>=0; i--)
            {
                sql = Regex.Replace(sql, "(" + variables[i].Replace("=", "")+")", values[i], RegexOptions.Singleline);
            }
            return sql;     
        }

        return @"Unknown sql query format.";
    }
}

Answer 3

我一直在寻找类似的东西，所以我在 LinqPad 中使用它，只需将 sp_executesql 语句复制到剪贴板并在 LinqPad 中运行代码。 它输出 SQL 语句。

void Main()
{
    ConvertSql(System.Windows.Forms.Clipboard.GetText()).Dump();
}

private static string ConvertSql(string origSql)
{
  string tmp = origSql.Replace("''", "~~");       
  string baseSql;
  string paramTypes;
  string paramData = "";
  int i0 = tmp.IndexOf("'") + 1;
  int i1 = tmp.IndexOf("'", i0);
  if (i1 > 0)
  {
      baseSql = tmp.Substring(i0, i1 - i0); 
      i0 = tmp.IndexOf("'", i1 + 1);
      i1 = tmp.IndexOf("'", i0 + 1);
      if (i0 > 0 && i1 > 0)
      {
          paramTypes = tmp.Substring(i0 + 1, i1 - i0 - 1);
          paramData = tmp.Substring(i1 + 1);
      }
  }
  else
  {
      throw new Exception("Cannot identify SQL statement in first parameter");
  }

  baseSql = baseSql.Replace("~~", "'");  
  if (!String.IsNullOrEmpty(paramData))  
  {
      string[] paramList = paramData.Split(",".ToCharArray());
      foreach (string paramValue in paramList)
      {
          int iEq = paramValue.IndexOf("=");
          if (iEq < 0)
              continue;
          string pName = paramValue.Substring(0, iEq).Trim();
          string pVal = paramValue.Substring(iEq + 1).Trim();
          baseSql = baseSql.ReplaceWholeWord(pName, pVal);
      }
  }

  return baseSql;
}

public static class StringExtensionsMethods
{
   /// <summary>
   /// Replaces the whole word.
   /// </summary>
   /// <param name="s">The s.</param>
   /// <param name="word">The word.</param>
   /// <param name="replacement">The replacement.</param>
   /// <returns>String.</returns>
   public static String ReplaceWholeWord(this String s, String word, String replacement)
   {
       var firstLetter = word[0];
       var sb = new StringBuilder();
       var previousWasLetterOrDigit = false;
       var i = 0;
       while (i < s.Length - word.Length + 1)
       {
           var wordFound = false;
           var c = s[i];
           if (c == firstLetter)
               if (!previousWasLetterOrDigit)
                   if (s.Substring(i, word.Length).Equals(word))
                   {
                       wordFound = true;
                       var wholeWordFound = true;
                       if (s.Length > i + word.Length)
                       {
                           if (Char.IsLetterOrDigit(s[i + word.Length]))
                               wholeWordFound = false;
                       }

                       sb.Append(wholeWordFound ? replacement : word);

                       i += word.Length;
                   }

           if (wordFound) continue;

           previousWasLetterOrDigit = Char.IsLetterOrDigit(c);
           sb.Append(c);
           i++;
       }

       if (s.Length - i > 0)
           sb.Append(s.Substring(i));

       return sb.ToString();
   }
}

Answer 4

另一种解决方案是直接在查询中替换参数值
（不完全是您所要求的，但可能对其他人有用）：

https:// /code.msdn.microsoft.com/windowsdesktop/spExecuteSql-parser-1a9cd7bc

我从：

exec sp_executesql N'UPDATE Task SET Status = @p0, Updated = @p1 WHERE Id = @p2 AND Status = @p3 AND Updated = @p4',N'@p0 int,@p1 datetime,@p2 int,@p3 int,@p4 datetime',@p0=1,@p1='2015-02-07 21:36:30.313',@p2=173990,@p3=2,@p4='2015-02-07 21:35:32.830'

到：

UPDATE Task SET Status = 1, Updated = '2015-02-07 21:36:30.313' WHERE Id = 173990 AND Status = 2 AND Updated = '2015-02-07 21:35:32.830'

这使得它更容易理解。

可以通过传递文件参数或复制剪贴板中的 sp_executesql 来使用该页面上的控制台应用程序，运行应用程序，然后从剪贴板粘贴生成的 SQL。

更新：

还可以将 SQL 格式化程序添加到该解决方案中，以便于阅读：

http ://www.nuget.org/packages/PoorMansTSQLFormatter/

newSql = ConvertSql(Clipboard.GetText());
var formattedSql = SqlFormattingManager.DefaultFormat(newSql);
Clipboard.SetText(formattedSql);

Answer 5

您可以使用此 Azur 数据工作室扩展。 它基于@Matt Roberts 仓库。
https://github.com/PejmanNik/sqlops- spexecutesql-to-sql/releases/tag/0.0.1

Answer 6

Sql Prompt 最近获得了此功能（2017-02-06）。 选择文本并在上下文菜单中查找“Inline EXEC”。 一定要爱提示:)

Answer 7

我不知道现有的插件可以做到这一点。 但你可以创建一个:)

一些正则表达式和一些字符串连接，然后将其卖给 Vinko 和其他寻找此功能的人。

如果您想深入了解这一点，这里有一些有关创建 SSMS 插件的信息：
http: //sqlblogcasts.com/blogs/jonsayce/archive/2008/01/15/building-a-sql-server-management-studio-addin.aspx

Answer 8

我也遇到了这个问题，并编写了简单的应用程序来解决它 - ClipboardSqlFormatter。 这是一个托盘应用程序，用于侦听剪贴板输入事件并尝试检测动态 sql 并将其转换为静态 sql。

您需要做的就是复制动态 sql（例如从 sql profiler）并粘贴到文本编辑器 - 粘贴的 sql 将是静态 sql :)

例如，如果复制的 sql 为：

exec sp_executesql N' SELECT "obj"。 “CreateDateTime”，“obj”。“LastEditDateTime”来自 LDERC
“doc”INNER JOIN LDObject“obj”ON（“doc”。“ID”=“obj”。“ID”）LEFT OUTER JOIN LDJournal
"ContainerID.jrn" ON ("doc"."JournalID" = "ContainerID.jrn"."ID") WHERE ("doc"."ID"
= @V0 AND ( "doc"."StateID" <> 5 AND "ContainerID.jrn"."Name" <> ''隐藏日记''
) ) ',N'@V0 bigint',@V0=6815463'


然后粘贴的 SQL 将是：

SELECT "obj"."CreateDateTime"
,“obj”。“最后编辑日期时间”
来自 LDERC“文档”
INNER JOIN LDObject "obj" ON ("doc"."ID" = "obj"."ID")
左外连接 LDJournal "ContainerID.jrn" ON ("doc"."JournalID" = "ContainerID.jrn"."ID")
在哪里 （
“文档”。“ID”= 6815463
和 （
“doc”.“StateID”>> 5
AND“ContainerID.jrn”。“名称”>> 《隐藏的日记》
）
）

Answer 9

这是我用来检查 NHibernate 查询的简单 UI。 使用了一些正则表达式、解析和sqlformat.org API来美化sql。

<html>
<head>
    <script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
    <script type="text/javascript">
        $(function(){
            $("#btn-format").on("click", () =>{
                var insql = $("#textarea-in").val();        

                var regex = new RegExp("exec sp_executesql N'(?<command>.+?(?='(,N'$)?))'(,\s*N'(?<types>.+?(?=',))',\s*(?<vals>.+))?");
                var groups = insql.replace(/\n|\r/g, "").match(regex).groups;

                var outsql = "";
                if (groups.types)
                {
                    var types = groups.types.match(/@[^\s]+ \w+(\([\w\d,]+\))?/g);
                    for (const typeDeclaration of types) {
                        outsql = outsql + 'declare ' +  typeDeclaration + '\n';
                    }
                    outsql = outsql + '\n';
                    for (const setVal of groups.vals.split(',')) {
                        outsql = outsql + 'set ' +  setVal + '\n';
                    }
                    outsql = outsql + '\n';
                }
                $.ajax({
                    url: 'https://sqlformat.org/api/v1/format',
                    type: 'POST',
                    dataType: 'json',
                    crossDomain: true,
                    data: {
                        sql: groups.command, reindent: 1
                    },
                    success: (data) => {
                        outsql = outsql + data.result;
                        $("#textarea-out").val(outsql);
                    },
                    error: () =>{
                        outsql = outsql + '-- No format happened. See browser console for details \n';
                        outsql = outsql + groups.command;
                        $("#textarea-out").val(outsql);
                    }
                });        
            })    
        });
    </script>        
</head>
<body>
    <textarea id="textarea-in" style="width: 100%; height: 48%;" class="form-control" placeholder="type 'exec sp_executesql...' here"></textarea>
    <br/>
    <button id="btn-format">Format</button>
    <br/>
    <textarea id="textarea-out" style="width: 100%; height: 48%;" class="form-control"></textarea>
</body>

在 Fiddle 中测试

注意：如果查询中包含单引号，则不起作用

Answer 10

结论：我注意到这仍然引起了一些关注，因此我将在此处添加有关我的最终解决方案的详细信息。

事实证明，没有什么比为自己做事更好的了。 我创建了一个简单的控制台应用程序，它解析我的存储过程并输出我想要的内容。 通过将其添加到外部工具列表中，并将当前文件名作为参数传递，我可以使用以下内容来删除并重新排列我需要的内容。

在使用中，我会添加一个新的sql文件，粘贴sql，保存它，然后运行外部工具。 完成后，IDE 会要求我重新加载文件。 噗，不再有存储过程了。

我确实注意到，这可能不适用于每个executesql 语句，因此如果它不能满足您的需求，您必须进行修改。

using System;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;

class Program
{
    const string query = "query";
    const string decls = "decls";
    const string sets = "sets";

    static void Main(string[] args)
    {
        try
        {
            var text = File.ReadAllText(args[0]);
            if (string.IsNullOrEmpty(text))
            {
                Console.WriteLine("File is empty.");
            }

            var regex = new Regex(
                @"exec sp_executesql N'(?<" + query + ">.*)',N'(?<" + decls + ">[^']*)',(?<" + sets + ">.*)",
                RegexOptions.Singleline);
            var match = regex.Match(text);

            if (!match.Success || match.Groups.Count != 4)
            {
                Console.WriteLine("Didn't capture that one.");
                Console.Read();
                return;
            }

            var sb = new StringBuilder();
            sb.Append("DECLARE ").AppendLine(match.Groups[decls].Value);
            foreach (var set in match.Groups[sets].Value
                         .Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries))
                sb.Append("SET ").AppendLine(set);
            sb.AppendLine(match.Groups[query].Value.Replace("''", "'"));
            File.WriteAllText(args[0], sb.ToString());
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.ToString());
            Console.Read();
        }
    }
}

Answer 11

我改进了之前的答案之一，特别是改进了正则表达式，以支持无参数的查询。 这是我的 PowerShell 脚本形式的选项，它使用 Windows 剪贴板作为输入和输出：

$regex = "(?s)^exec sp_executesql N'(?<query>.*?[^'])'(?:,N'(?<decls>.*?[^'])',(?<sets>.*))?$"

$inputText = [string](Get-Clipboard)
if (!($inputText -match $regex)) {
    return
}

$resultBuilder = [System.Text.StringBuilder]::new()
if ($matches["decls"]) {
    $resultBuilder.AppendLine("DECLARE $($matches["decls"])")
    $matches["sets"].Split(',') | ForEach-Object {
        $resultBuilder.AppendLine("SET $_");
    }
}
$resultBuilder.AppendLine($matches["query"].Replace("''", "'"));
Set-Clipboard $resultBuilder.ToString()