How to format Microsoft Dynamics GP SQL Stored Procedures


David Meego - Click for blog homepageHave you ever decided to have a look at one of the SQL stored procedures in your Dynamics GP databases?

If you have, you would have noticed that they are extremely difficult to read. This is because comments and formatting white space are removed by Dynamics Utilities when it creates the stored procedures. This step was taken to improve performance of the resulting code, but has the side effect of making the code impossible to read.

In the past, when I have needed to analyse code in a stored procedure, I have manually formatted the code as I read it so I can see the levels of indentation and individual commands.

A little while ago, my friend Steve Gray talked about Formatting SQL. In his post he mentioned the free Format SQL site from Redgate (the people behind the SQL Prompt tool). I had heard of SQL Prompt before, but did not require all the features it offered and did not want to buy anything. I also found that the free Format SQL site did not work well when pasting in scripts via the clipboard.

I just wanted a free online tool to format cut and pasted text from a SQL stored procedure so I can read it.

So today, I used that wonderful invention…. an internet search engine, and found a number of free tools that work well:

In no particular order:

Enjoy

David

17-Jan-2013: Just a follow up comment. Poor SQL is my preferred option, but mainly because it offers a free plug-in for SQL Server Management Studio and Visual Studio.

Comments (9)

  1. Vaidy Mohan says:

    Thank you so much, David. The pain to go thru' unformatted SQL code. Not just GP's source procedures. But any SQL code written by some other developer. Those who do not have the habit of aligning their SQL Code, can now at least use the mentioned utilities and get their code aligned properly. This is very important from a developer's perspective. Thanks again.

  2. Ron Harris says:

    Dave to the rescue again

  3. David Musgrave says:

    Feedback so far indicates that the Poor SQL option does the best job. Depends if you like commas shown before variables or after.  In my opinion, before works better as it is easier to comment a line with double dash (–).

    David

  4. Adam Page says:

    If anyone prefers a nice SSMS plug in http://www.ssmstoolspack.com/ does a nice job an is a free tool. Not quite as robust as Redgate, but does a nice job. Also , each feature can be turned off if you only want certain functions.

  5. David Musgrave says:

    Hi Adam

    Thanks for the feedback.

    David

  6. David Musgrave says:

    Posting by Vaidy Mohan at Dynamics GP – Learn & DIscuss

    vaidymohan.com/…/formatting-sql-procedures

  7. David Musgrave says:

    Posting by Mark Polino at DynamicAccounting.net

    msdynamicsgp.blogspot.com.au/…/how-to-format-microsoft-dynamics-gp-sql.html

  8. Vrushali says:

    Thank you..

  9. Jack says:

    I have gone through below tool…

    http://www.textformat.in

    Really it gives excellent feature like text formatting and query validating (basic validation).

    It also did a query formatting with full screen.

    Like this tool.