Wednesday, March 2, 2011

Formatting SQL Server TSQL with Oracle's SQL Developer

Note that in the time since I wrote this post, another (much better) option for formatting SQL Server script has come up.
If you do much troubleshooting on Microsoft SQL Server, you inevitably will end up having to deal with a poorly-formatted (and hard-to-read) SQL statement from a query using sys.dm_exec_sql_text or the like.
There are lots of online formatters out there, though I've had decidedly mixed results with them. There also are a lot of add-in and standalone products available that will do a good at this. Here's another one to add to your list, until SSMS includes a formatting feature: Oracle's SQL Developer.
SQL Developer is a free download (here) that will, in fact, connect to SQL Server instances. While I do not use it for my day-to-day SQL Server administration tasks, I use it regularly to reformat SQL that I've pulled from the DMVs.


Here's how it looks using the following SQL Agent task SQL.
(@P1 int,@P2 uniqueidentifier,@P3 int)UPDATE msdb.dbo.sysjobactivity SET run_requested_date = DATEADD(ms, -DATEPART(ms, GetDate()), GetDate()), run_requested_source = CONVERT(sysname, @P1), queued_date = NULL, start_execution_date = NULL, last_executed_step_id = NULL, last_executed_step_date = NULL, stop_execution_date = NULL, job_history_id = NULL, next_scheduled_run_date = NULL WHERE job_id = @P2 and session_id = @P3
First we paste this in to a new page in SQL Developer:
And then we hit CTRL-F7 (or right-click and select "Format"):
Which gives us very nicely formatted SQL. One gotcha here: SQL Developer doesn't know what to do with the 'GO' statement, so it puts it on the same line as other SQL commands. This will keep your code from running, so there's one piece of cleanup that is necessary when using SQL Developer.

No comments:

Post a Comment

Thanks for leaving a comment!