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

    Quick extended properties with SQL Prompt

    I’ve been experimenting with Extended Properties, and I found myself slightly annoyed by the syntax of adding and updating Extended Properties. I decided to take advantage of SQL Prompt to store the commonly used code for adding and updating properties. Adding Properties I’m a big fan of naming the snippets the first thing that comes to mind.

  • Article

    How to reformat a database in one operation

    Inherited a database from another team? Changed your team policy on the way that you format SQL? What’s to stop you formatting the code of an entire database nicely, when you’re developing it? It can be done, but the process can take longer than you expect. This article will demonstrate a simple 3-step approach to

  • Article

    The risks of using EXECUTE ('SQL Script')

    Using dynamic SQL is unavoidable at times, but it is reckless to execute dynamic SQL that is created directly from strings that include values that change at execution time. It can allow SQL Injection and it is also inefficient. SQL Prompt’s code analysis rule, BP013 will alert you to use of Execute(<string>), to execute a

  • Article

    Cleaning up common T-SQL coding issues with SQL Prompt

    Some SQL coding habits are just annoying. Commas in front of column names? No way! Others are actively harmful; they’ll make your code error prone, harder for others to read and understand, and even harder to edit without making mistakes. This article covers five harmful problems that I see regularly in T-SQL code, and shows

  • 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