Freigeben über

How to format Microsoft Dynamics GP SQL Stored Procedures

  • Artikel

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

  • Anonymous
    April 18, 2012
    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.

  • Anonymous
    April 18, 2012
    Dave to the rescue again

  • Anonymous
    April 19, 2012
    The comment has been removed

  • Anonymous
    April 21, 2012
    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.

  • Anonymous
    April 22, 2012
    Hi Adam Thanks for the feedback. David

  • Anonymous
    April 29, 2012
    Posting by Vaidy Mohan at Dynamics GP - Learn & DIscuss vaidymohan.com/.../formatting-sql-procedures

  • Anonymous
    April 29, 2012
    Posting by Mark Polino at DynamicAccounting.net msdynamicsgp.blogspot.com.au/.../how-to-format-microsoft-dynamics-gp-sql.html

  • Anonymous
    September 22, 2014
    Thank you..

  • Anonymous
    October 16, 2014
    I have gone through below tool... 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.