CODEWITHSUNDEEP
sql-servermacrosformatssmssp-executesql
user1228
user1228

Reputation:

Easy way to convert exec sp_executesql to a normal query?

When dealing with debugging queries using Profiler and SSMS, its pretty common for me to copy a query from Profiler and test them in SSMS. Because I use parameterized sql, my queries are all sent as exec sp_executesql queries. 

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

I'll take this and convert it into a normal query for ease of editing (intellisense, error checking, line numbers, etc):

DECLARE @someParameter tinyint
SET @someParameter = 2

/*some query here*/

Of course, the bigger and more complex the query, the harder to do this. And when you're going back and forth multiple times, it can be a pain in the ass and soak up lots of time.

Is there an easy (e.g., macro command) way to convert muh executesql into something more convenient?

Upvotes: 32

Views: 11996

Answers (11)

user1228
user1228

Reputation:

Conclusion: I note this still gets a little attention, so I'll add details here for what my eventual solution was.

It turns out that nothing beats doing it for yourself. I created a simple console app that parsed my stored procedure and spit out what I wanted. By adding it to the list of external tools, and passing the current filename as an argument, I could use the following to strip out and rearrange what I needed.

In use, I'd add a new sql file, paste in the sql, save it, then run the external tool. After it completes, the IDE asks me to reload the file. Poof, no more stored procedure.

I do note that this may not work with every executesql statement, so you'll have to modify if it does not meet your needs.

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

Upvotes: 1

Gennadii Saltyshchak
Gennadii Saltyshchak

Reputation: 637

I have improved one of previous answers particularly refining regular expression in order to support queries without parameters. Here is my option in form of PowerShell script, which uses Windows clipboard both as input and as output:

$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()

Upvotes: 0

iliakolesnikov
iliakolesnikov

Reputation: 66

Here is simple UI that i use to inspect NHibernate queries. Some regex, parsing and sqlformat.org API to beautify sql is used.

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

Test in Fiddle

Note: Will not work, if you have single quotes in query

Upvotes: 1

Pejman Nikram
Pejman Nikram

Reputation: 590

You can use this Azur data studio extension. it based on @Matt Roberts repo. https://github.com/PejmanNik/sqlops-spexecutesql-to-sql/releases/tag/0.0.1

enter image description here

Upvotes: 3

Alex Vazhev
Alex Vazhev

Reputation: 1461

I spent a little time and created a small modification of Matt Roberts / Wangzq solutions without DECLAREs section, you can try it on .NET Fiddle or download LINQPad 5 file.

Input:

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

Output:

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

Code:

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

Upvotes: 10

Alex141
Alex141

Reputation: 1203

I faced with this problem too and wrote simple application for solving it - ClipboardSqlFormatter. This is a tray application that listens clipboard input events and tries to detect and convert dynamic sql to static sql.

Anything you need is to copy dynamic sql (from sql profiler for example) and paste to text editor - pasted sql will be a static sql :)

For example, if copied sql is:

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'

then pasted sql will be:

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

Upvotes: 1

buckley
buckley

Reputation: 14079

Sql Prompt got this feature recently (2017-02-06). Select the text and look for "Inline EXEC" in the context menu. Gotta love Prompt :)

Upvotes: 2

Toby
Toby

Reputation: 83

I was looking for something similar so I use this in LinqPad, just copy sp_executesql statement to the clipboard and run the code in LinqPad. It outputs the SQL statement.

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

Upvotes: 6

Răzvan Flavius Panda
Răzvan Flavius Panda

Reputation: 22106

Another solution which replaces the parameter values directly in the query (not exactly what you asked for but it might prove useful to others):

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

I goes from:

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'

to:

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'

which makes it easier to understand.

The console application on that page can be used by passing a file parameter or copying the sp_executesql in the clipboard, running the app and then pasting the resulting SQL from the clipboard.

Update:

An SQL formatter can also be added to that solution for easier readability:

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

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

Upvotes: 4

Matt Roberts
Matt Roberts

Reputation: 26887

I spent a little time making an simple script that did this for me. It's a WIP, but I stuck a (very ugly) webpage in front of it and it's now hosted here if you want to try it:

http://execsqlformat.herokuapp.com/

Sample input:

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

And the output:

BEGIN
DECLARE @level tinyint;

SET @level = 109;

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

The formatting of the actual SQL statement once I've plucked it from the input is done using the API at http://sqlformat.appspot.com

Upvotes: 46

Yvo
Yvo

Reputation: 19263

I am not aware of an existing Add-In that can do this. But you could create one :)

A few regular expressions and some string concatenation and after that sell it to Vinko and other souls looking for this functionality.

If you're feeling like diving into this, here is some information on creating an SSMS addin: http://sqlblogcasts.com/blogs/jonsayce/archive/2008/01/15/building-a-sql-server-management-studio-addin.aspx

Upvotes: 1

Related Questions