Unwrap sp_executesql
Frequently I need to debug application generated SQL obtained via Profiler. Parameterized queries are often implemented with sp_executesql.
I love using Format SQL to make a single line monster query into something I can actually read. But when it's stuck in an spexecutesql call the format function doesn't touch it. I have to:
Remove "Exec spexecutesql".
Remove the apostrophes around the query SQL. For big queries this is really painful, especially if the query itself contains apostrophes.
Remove the apostrophes around the parameter defenitions.
Move the parameter definitions to the top and add a DECLARE.
Move the parameter value assignments below the declare and add a SELECT.
Slightly trivial example
exec sys.sp_executesql N'select * from sys.objects where [name]=@objectName and [type]=@objectType', N'@objectName SYSNAME, @objectType CHAR(2)', @objectName = N'sysrscols', @objectType = 's'
I would like this feature to transform it to
DECLARE @objectName SYSNAME, @objectType CHAR(2)
SELECT @objectName=N'sysrscols', @objectType='s'
select * from sys.objects where [name]=@objectName and [type]=@objectType
Then regular formatting rules would apply.
The kinds of queries I regularly debug much bigger and complex. You can imagine how much pain this would save me :)
Chris Rickard
shared this idea
This feature was released in version 7.3 of SQL Prompt.
If there are any missing features please let us know by creating a new suggestion.
Kind Regards,
The Prompt Team
-
Chris Rickard
commented
Haven't been around these forums in a while.. Wanted to let Red Gate know that I'm very grateful they've implemented this. It's a great time saver!
-
Anonymous
commented
Yay, about time :) This should be awesome!
-
Alex
commented
Simple sp_executesql unwrapper: https://dotnetfiddle.net/CH6kmF
-
Tsahi
commented
You can set the variable values with the DECLARE, such as
DECLARE @objectName SYSNAME = N'sysrscols'or use SET instead of SELECT.
-
Toby
commented
It would also be good to convert a normal query to a dynamic query so it can be used by sp_executesql
-
Jack
commented
It would be great if this also worked for sp_prepare, sp_prepexec, sp_cursorprepare. There may be a couple others. 2 of the applications I have to troubleshoot use these and I spend too much time trying to cleanup the code so I can figure out what the query is really doing.
-
Anonymous
commented
Yep this would be a very useful feature. I have written a script to do this for me but it is fairly buggy . It seems like this would be an excellent feature for sql prompt to build in to the formatting options.