27 June 2018
27 June 2018

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.

Finding SQL Code Smells using SQL Prompt

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.

Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly in SQL Server Management Studio and Visual Studio.

Find out more

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Choosing Between Table Variables and Temporary Tables (ST011, ST012)

    People can, and do, argue a great deal about the relative merits of table variables and temporary tables. Sometimes, as when writing functions, you have no choice; but when you do you’ll find that both have their uses, and it’s easy to find examples where either one is quicker. In this article, I’ll explain the

  • Article

    SQL Prompt Hidden Gems: The SSMS Results Pane

    Within SSMS, when executing a query, we can view the query results in a grid, or tabular, format (Ctrl-D), as plain text (Ctrl-T), or we can write them to a plain-text reporting (.rpt) file using Ctrl-Shift-F. However, over my 20 plus years as a SQL programmer, I’ve often needed to take these results and use

  • Article

    How to record T-SQL execution times using a SQL Prompt snippet

    A SQL Prompt snippet is a pre-defined block of code, which we can insert at the current cursor position, or around currently-selected SQL text, in an SSMS query pane. DBAs use them to store all their most commonly-used checks and routines. It’s often useful to have your favorite troubleshooting scripts available to you within a

  • Article

    Testing the Performance of Individual SQL Statements within a Batch using SQL Prompt

    This article is all about using a SQL Prompt snippet to create and run a test harness on a batch of SQL code, to provide performance data. Behind the scenes, the snippet creates and starts an Extended Events event session, inserts and runs the text of the batch of SQL you’ve selected in SSMS, and

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt.

    As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly