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

    The SQL Prompt Functionality Finder

    You’ve got SQL Prompt, but are you aware of all the things it can do and how to get Prompt to do it? In this article, Phil Factor provides a handy table to make it obvious. As soon as you open SQL Prompt and start typing, its IntelliSense suggestion window will pop up, offering context-specific

  • Article

    Quick SQL Prompt tip – using the ii snippet

    SQL Prompt includes a number of snippets by default that can help you quickly write T-SQL code. These are templates of code that users use regularly. One of the more popular snippets is the “ii” snippet that helps with inserting data into a table. If I type “ii” and hit tab, I quickly get the

  • 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

    How to Test SQL Server Functions and Procedures using SQL Prompt

    When writing functions or procedures, a common chore is to devise and implement the tests that ensure that the routine always works as expected. The best way to do this is to define the tests in a batch that is attached to the CREATE or ALTER script. This isn’t just to prevent you from making

  • Article

    Avoid T-SQL Technical Debt using SQL Prompt Code Analysis

    SQL Prompt’s Code Analysis feature helps you discover code issues and hidden pitfalls during code development, as you type. It also provides tips for improving your code, and includes links to documentation that offers advice on the about each of the supported rules, so you can decide on the best course of action. This is

  • Article

    Finding code smells using SQL Prompt: old-style join syntax (ST001)

    SQL Prompt implements a static code analysis rule, ST001, which will check code automatically, during development and testing work, for occurrences of non-ANSI standard JOIN syntax. The “old style” Microsoft/Sybase JOIN style for SQL, which uses the =* and *= syntax, has been deprecated and is no longer used. Queries that use this syntax will