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

Related posts

Also in Hub

The ‘Right to be Forgotten’ and Data Masker for SQL Server

The right to be forgotten is one of the main features of new data protection legislation across the globe. Under Article 17 of one such piece of legislation in Europe, the GDPR, individuals have the r...

Also in SQL Prompt

SQL Prompt code analysis: avoiding the old-style TOP clause

The syntax of the TOP clause insists on a bracketed, or parenthesized, expression as its first argument, so that TOP 34 should, strictly speaking be TOP (34), although if you are just supplying a nume...

Also in Product learning

Scaling SQL Monitor to Large SQL Server Estates

Most organizations are finding that the size and number of databases that need to be monitored is increasing, unlike the number of people available to do the work. Now that it is possible for the orga...

Also about SQL Prompt

Avoid use of the MONEY and SMALLMONEY datatypes

The MONEY data type confuses the storage of data values with their display, though its name clearly suggests the sort of data it holds. It is proprietary to SQL Server and allows you to specify moneta...

Also about static code analysis

SQL Code Analysis from a PowerShell Deployment Script

Database code analysis becomes more important as the team doing the database development gets bigger and more diverse in skills. Hard-working database developers sometimes check-in 'temporary' develop...

Also about SQL code smells

Finding code smells using SQL Prompt: Asterisk in SELECT list

Using SELECT * FROM within IF EXISTS statements are fine, but in other contexts it causes several problems. For example, INSERT INTO…SELECT * FROM statement assumes the columns are in a particular o...