代码之家 › 专栏 › 技术社区 › Will

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

sp-executesql ssms format macros sql-server

Will · 技术社区 · 15 年前

当使用profiler和ssms处理调试查询时,从profiler复制查询并在ssms中测试它们是非常常见的。因为我使用参数化SQL,所以我的查询都作为exec sp_executesql查询发送。

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

为了便于编辑(intellisense、错误检查、行号等),我将把它转换为普通查询:

DECLARE @someParameter tinyint
SET @someParameter = 2

/*some query here*/

当然,查询越大、越复杂,就越难执行此操作。当你来回多次的时候,这会让你的屁股很痛,而且会吸收很多时间。

有没有一种简单的(例如宏命令)方法可以将muh-executesql转换成更方便的方法?

9 回复 | 直到 6 年前

Yvo 15 年前

我不知道可以这样做的现有外接程序。但你可以创建一个:)

一些正则表达式和一些字符串连接,然后将其出售给vinko和其他寻找此功能的灵魂。

如果您想深入了解这一点,以下是有关创建SSMS加载项的一些信息: http://sqlblogcasts.com/blogs/jonsayce/archive/2008/01/15/building-a-sql-server-management-studio-addin.aspx

Jarrod Dixon IndianaJones 11 年前

我花了点时间做了一个简单的脚本,为我做了这个。这是一个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语句的格式,就可以使用API在 http://sqlformat.appspot.com

Toby 9 年前

我在找类似的东西,所以我在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();
   }
}

Will L Alex Vazhev 6 年前

我花了点时间,对Matt Roberts/Wangzq解决方案做了一个小修改,没有声明部分,你可以试试看。 .NET Fiddle 或 download LINQPad 5 file .

输入:

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.";
    }
}

RÄzvan Flavius Panda 10 年前

另一个直接替换查询中参数值的解决方案 (不完全是你要求的,但可能对其他人有用):

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

buckley 8 年前

SQL Prompt最近获得此功能(2017-02-06)。选择文本并在上下文菜单中查找“inline exec”。要爱提示:)

Alex141 7 年前

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

您需要的任何东西都是复制动态SQL(例如从SQL事件探查器)并粘贴到文本编辑器-粘贴的SQL将是静态SQL:。

例如,如果复制的SQL是:

exec sp_executesql N' SELECT "obj"."CreateDateTime", "obj"."LastEditDateTime" FROM 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" <> ''Hidden journal'' ) ) ',N'@V0 bigint',@V0=6815463'

然后粘贴的SQL将是:

SELECT "obj"."CreateDateTime" ,"obj"."LastEditDateTime" FROM 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" = 6815463 AND ( "doc"."StateID" <> 5 AND "ContainerID.jrn"."Name" <> 'Hidden journal' ) )

marc_s HarisH Sharma 7 年前

结论:我注意到这仍然会引起一点关注,所以我将在这里详细说明我的最终解决方案。

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

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

异常处理和其他东西都被剥离了,我注意到这可能不适用于 每一个 executesql语句,因此如果它不满足您的需要,则必须进行修改。

var text = File.ReadAllText(args[0]);
if(string.IsNullOrEmpty(text))
{
    Console.WriteLine(
        "File is empty; try saving it before using the hillbilly stored procedure decoder");
}
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();
// declares go on top
sb.Append("DECLARE ").AppendLine(match.Groups["decls"].Value);
// split out our sets, add them one line at a time
foreach(var set in match.Groups["sets"]
                   .Value.Split(new char[] { ',' }, 
                   StringSplitOptions.RemoveEmptyEntries))
    sb.Append("SET ").AppendLine(set);
// Add our query, removing double quotes
sb.AppendLine(match.Groups["query"].Value.Replace("''", "'"));
File.WriteAllText(args[0], sb.ToString());

Pejman Nikram 6 年前

您可以使用这个Azur Data Studio扩展。它基于@matt roberts repo。 https://github.com/PejmanNik/sqlops-spexecutesql-to-sql/releases/tag/0.0.1