This is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.
Have 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:
- T-SQL Tidy (http://www.tsqltidy.com/)
- Instant SQL Formatter (http://www.dpriver.com/pp/sqlformat.htm)
- Poor SQL (http://poorsql.com/)
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.
This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.
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.
Dave to the rescue again
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
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.
Hi Adam
Thanks for the feedback.
David
Posting by Vaidy Mohan at Dynamics GP – Learn & DIscuss
vaidymohan.com/…/formatting-sql-procedures
Posting by Mark Polino at DynamicAccounting.net
msdynamicsgp.blogspot.com.au/…/how-to-format-microsoft-dynamics-gp-sql.html
Thank you..
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.