Tom Walsh

27 June 2018

• SQL Prompt
• SQL Code Analysis

Improving legacy code using SQL Prompt code analysis

Code analysis is a useful technique for identifying potential issues in your code base, and ensuring that it adheres to your pre-defined standards, so that you can release your software product with more confidence. SQL Prompt 9.2 and later also allows you to view a whole list of code issues detected in a SQL script, group them by type, and export them. This makes it much easier for a team to perform code reviews, or analyse a legacy code base for possible refactoring improvements.

Prevent technical debt: code analysis as-you-type

SQL Prompt will perform static code analysis on your T-SQL code, as you type it into a query window in SSMS or Visual Studio. It parses the code, as a compiler would, but rather than compile the code, it checks the syntax for compliance with a built-in set of rules. SQL Prompt highlights each issue, or ‘code smell’, by underlining the offending line of code with a green squiggle, as demonstrated in Figure 1.

Figure 1

Developers can then see easily any issues they’ve introduced, and get tips on how they can fix each one. This ensures that all code committed to source control complies with the common set of coding standards and practices, and it prevents issues from creeping into the build that might, now or eventually, cause performance, maintenance, or security problems. Catherine Wilhelmsen explores this use of Prompt’s code analysis in her article, Avoid T-SQL Technical Debt using SQL Prompt Code Analysis.

This approach is great for new code, but becomes a little cumbersome when dealing with legacy code and longer scripts, during code reviews and refactoring efforts, as you must scroll through the whole script, keeping an eye out for the each of the elusive green underlines.

Remove existing technical debt: list of code analysis issues for legacy code

SQL Prompt 9.2, and later, extend the code analysis feature to include a list of code analysis issues, designed to make it much easier to do code reviews, and to analyse and improve legacy code. It allows users to analyse an entire script, no matter how large, and see a list of all the issues contained within it.

Go to the SQL Prompt menu, and select SQL Prompt -> Show List of Code Analysis Issues…, to open up a full list of code issues for the chosen script, sorted in the order in which they appear in the file, as shown in Figure 2.

Figure 2

At the top, you’ll see the total number of issues in the file, in this case, 732. This acts as a worklist for improving overall code quality. If you double-click on an issue, you’ll see that line highlighted in the code, and you can view more details about the issue, as described in Frederico Jeronimo’s article, How you can improve your SQL with code analysis in SQL Prompt.

We are currently in the process of overhauling our online code analysis documentation to provide more in-depth information on code analysis issues, including examples illustrating how they can be solved.

Figure 3

Grouping code smells by type

Click the Group by: dropdown, then select Issue, and the code issues SQL Prompt has identified, in the file, will be grouped by type, with a count of the number of occurrences of each type. Simply click on an issue type to reveal each individual occurrence.

Figure 4

Export a list of code smells

To export a list of issues to a CSV file, click on the Export link at the top of the panel, as shown by Figure 5, and then you can share it via Slack or email, or attach it to a ticket in your bug-tracking or task management software. This makes it easy to share updates about the health of certain sections of the database code, or to assign “clean-up” tasks, with each developer on a team assigned a set of issue types.

Figure 5

What’s Next?

SQL Prompt code analysis provides a quick and easy way to assess the overall health and quality of your SQL scripts. It will help the team prevent technical debt from entering the code base, to remove it from existing code during code reviews, and to ensure that the released code conforms to the team’s defined coding standards.

We’ll continue to develop these code analysis features, including allowing you to group the issues in different ways and export to different file types. We’re always keen to hear feedback from our users, so if you’d like to suggest how we should expand and improve this feature further, head on over to UserVoice and let us know.