As a consultant, I often perform debugging or enhancement on existing or legacy code. This involves seeing (and dealing with) one style of SQL coding to another. Some has coding standard and mostly consistent in the way all the scripts are written. Some has no coding standard or requires scrolling horizontally a few times to be able to read the code in its entirety (and a few times of scrolling to understand the code). These days, I know when to pick my battle. When the code looks messy, I would stop and use some sort of SQL format tool out there. Well formatted code (with a good degree of consistency) makes it easier for me to understand the flow of the code, and more often than not, I’d be less distracted by all the noise around the code. Simple noise such as mixture of upper and lower case (e.g. “SelECt”, “jOIn”, “alter PROCEDURE”) can be really distracting, from task at hand.
Well formatted code is as a fundamental element as the white space concept in the Design world. It increases promotes comprehension and increases attentiveness.
One of my favourites to format messy code would go to SQL Prompt. Once you download and install SQL Prompt, you will see SQL Prompt menu on SQL Server Management Studio (SSMS). This article reviews version 6.3.0 of SQL Prompt. The following menu items are definitely the key features for having readable code as well as quicker to troubleshoot other people’s code (and remember the keyboard short cuts too for easier access).
1. Format SQL (Ctrl+K Ctrl+Y)
Bellow is a screen shot of a snippet of messy SQL code before and after performing Format SQL command.
2. Apply Casing Options (Ctrl+B Ctrl+U)
Below is a screenshot of before and after performing Uppercase Keywords.
3. Qualify Object Names (Ctrl+B Ctrl+Q)
Bellow is a screen shot of a snippet of messy SQL code before and after performing Qualify Object Names command.
4. Expand Wildcards (Ctrl+B Ctrl+W)
Bellow is a screen shot of a snippet of messy SQL code before and after performing Expand Wildcards.
5. Summarize Script (Ctrl+B Ctrl+S)
Below is a screenshot taken when performing “Summarize Script” on an SSISDB stored procedure called catalog.set_environment_property. When you click on one of the tree element in the Summarize Script window, the corresponding code will be highlighted.
The first 3 are definitely designed for enhancing readability. The last 2 would definitely make it easier to navigate the code.
Style Customisation
Customising formatting style can be achieved from SQL Prompt > Options menu in SSMS. If you work with multiple teams and with multiple coding standard, SQL Prompt allows you to save a number of different styles.
Wouldn’t It Be Nice If …
The above shows you some of the great features of SQL Prompt. Now I will highlight a few tiny areas that SQL Prompt could improve on:
1. Qualify Object Names feature should also be able to change the case of the object names on the script to be exactly the same as what is in the database.
2. On the Summarize Script window, there is a way to select multiple nodes to “bookmark it” option. Quite often, we navigate a script to investigate something and continued by changing the required code. So, bookmarking the beginnings of the block of code of interest for modification would be beneficial.
It does not stop here of course!
The team at Red Gate does not stop here of course. They are enhancing SQL Prompt (and many other of their products) on regular basis. As an example, SQL Prompt v6.4 beta is cooking. This usually gives more confidence to users that the product is being supported.
Wrap Up
You don’t have to be a consultant to see the benefit of having clean and tidy code. Also, having a standardised format of SQL code also makes it easier to compare versions of code. SQL Prompt is a great tool for database developers to tidy up SQL Code to make it readable, as well as to navigate through long code with the “Summarize Script” feature.
Further Reading
Read other #SQLCoop stories on their experience with SQL Prompt here:
- Become a T-SQL Hero with SQL Prompt by Jeffrey Verheul
- Becoming a SQL Prompt Power User by Mickey Stuewe
- SQL Prompt – The Power Within by Chris Yates
No responses yet