Product articles SQL Prompt SQL Code Analysis
Exploring Auto-fix in SQL Code…

Exploring Auto-fix in SQL Code Analysis

Phil Factor presents a useful but slightly flawed 'table report' script as an adventure playground for exploring SQL Code analysis issues. He demonstrates use of the auto-fix feature, to arrive at a pristine script free from wavy green underlines.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

In the SQL Prompt menu of SSMS or Visual Studio, you can enable SQL code analysis. It performs static analysis of your code to draw your attention to any parts of it that might contain a code smell, or would cause someone reviewing your code to raise a quizzical eyebrow. If your code is anything like mine, it will become liberally decorated with green underlining that indicates where your code might need attention.

The flawed table reports script

I’ve prepared a script intended to illustrate code analysis and auto-fix. Before you try it out, make sure that you are on a recent version of SQL Prompt, otherwise it will be a frustrating experience. It is harmless but contains plenty of code issues, many of which SQL Prompt can auto correct, but a few of which it can’t. It is only possible to fix a problem if there is just one unambiguous way of solving it, and this is only true of a subset of the issues that SQL Prompt can detect.

This is working code that lists out all the tables of the current database, displaying the attributes of tables, such as the number of rows, the number of columns, the constraints and keys. You may find it useful, but it deliberately contains a whole lot of issues to serve as an adventure playground for learning SQL Prompt. To add a certain sense of irony, some of my ‘mistakes’ are a deliberate provocation for any automated system, in that they are, in fact, correct usage. I do this just to emphasize that these code analysis rules aren’t cast in stone but are merely suggestions. Sometimes your code may, for all the green underlining, be better than the corrected version.

Firstly, we’ll load the code into SSMS. Before you start to whistle through your teeth at the sight of it, remember that the issues are intentional in this case!

This will produce, for the old Pubs Database, the following result

Table report for SQL Server

Everywhere that SQL Prompt can detect a possible error, you’ll see a wavy green underline:

SQL Prompt highlight possible issues in your TSQL scripts

Reviewing and auto-fixing code issues

When you left click on a green underlined issue, a light bulb icon will appear in the sidebar. There are two versions of the icon:

  • Icon Description automatically generated for Issues that are automatically fixable
  • Icon Description automatically generated for Issues that cannot be fixed automatically

When you click on an orange light bulb, a menu appears with options to apply an auto-fix, show more details about the issue, or see a list of all issues in the script. For blue light bulbs, you’ll see only the latter two options.

We can demonstrate auto-fix easily. On line 31, we have a reproachful green underline on the declaration of @Type as varchar(2). I’ve clicked on it and selected Show issue details:

Investigating a code issue in SQL Prompt

Click the orange light-bulb icon again and select the auto-fix option, in this case ‘Use non variable-sized Data Type’ (it means ‘use a fixed-sized data type’):

Autofixing a code issue in SQL Prompt

…and as if by dark sorcery, the type is changed to char(2).

When you are working through code to tidy it up, it is worth having both Code Analysis windows visible, one to list all potential transgressions in your script (Show all Issues), and the other to explain each potential problem (Show issue details). The following screen shows both these panes for the potential ‘smell’ on line 30, warning of the use of a sql_variant datatype:

Investigating use of the sql_variant datatype

The use of SQL_Variant, in this case, is benign. It is returned by the ObjectPropertyEx. In most cases, this should be a converted to a bit via the expression, for example, Convert(BIT,ObjectPropertyEx (@CurrentObject_ID, N'TableHasForeignKey')), and the temporary table #TableReport should have BIT data types rather than SQL_Variant data types.

A quick scan through the list of all issues will give some surprises. SQL Prompt’s code analysis recognizes sins such as a “blind insert” (BP004 -INSERT without column list) in a temporary table but ignores it in a table variable. In fact, it is possible to hide a multitude of sins in a table variable, even though the harm they can cause to the integrity of the data is just as likely as when they’re in a temporary table. I originally wrote both as table variables but changed the second to a temporary table to attract the green underlines.

Acting on Code Analysis warnings in SQL Prompt

Generally, I like to switch on all the Code Analysis rules and fix anything in my code that causes a green underline, even if it isn’t really warranted within the context in which the code is to be run. In fact, I’d like Code Analysis to find problems in more places, such as table variables. I use the command-line version of the SQL code analysis engine as a preliminary to reviewing code for a database.

It is a common reaction for users to ask ‘why?’ when their code is called out for a rule violation. SQL is a very forgiving language because it was originally designed for analysts rather than developers. The idea of ‘best practices’ comes from experience in maintaining and debugging code. SQL Code can be made terse, but there is no relation between the elegance of code and its maintainability, or readability. SQL Code just needs to be easy for other team members as well as you.

I’ve covered the requirement and reasons for the most common rules in various articles. Some articles cover several similar rules at once.

The fixed table reports script

In case you feel tempted to use the ‘adventure Playground’ code to do real table reports, here is the actual code required. The temporary table and table variable were there just to test out table-based code issues. This code has no green underlines.


SQL Prompt’s code auto-fix feature can be a godsend to the busy developer, reducing the time and tedium of writing SQL. However, there are still a lot of problems that can’t be tackled by auto-fix and may require more time to investigate and resolve. Also, applying these code analysis auto-fixes is subtly different from using (Ctrl K, Ctrl Y) to fix formatting issues, in that auto-fix actually changes code.

A SQL Developer will want to get the general strategy right before fixing smaller SQL coding issues or style problems. It doesn’t pay to tidy up and correct code if the algorithm is wrong or inefficient because you’ll just have to go through the whole tidying up process again once when you subsequently come up with an entirely different and neater way of doing the job.

However, before code is delivered, it is best that all the code analysis issues are tidied up, and the quickest and easiest way of doing that is to use auto-fix. I hope that, in this article, I’ve illustrated a few nuances in the process. Sometimes, the absence of a wavy line doesn’t mean that a subtle problem doesn’t exist (as in table variables). Sometimes wavy green lines don’t mean that there is a problem (as with those SQL variants). Sometimes, by focusing one the minor details, you miss the bigger problem that the code is inefficient and slow.


Tools in this post

SQL Prompt

Write, format, and refactor SQL effortlessly

Find out more