Command line SQL Static Code Analysis

We have had a lot of requests from users asking us to make T-SQL Static Code Analysis available at build time and from the command line. The latest Visual Studio Team System 2008 Database Edition Power Tool release, adds this ability to the product through a new MSBuild task. In this blog post I will go through the step that you need to follow to enable and use T-SQL Static Code Analysis from the command line.

Step 0: Install the Power Tools

You first need to install the latest Power Tools for Visual Studio Team System 2008 Database Edition.

Step 1: Create a new database project or open an existing one

We need a database project, either use an existing one, or simple create a test project, from example using the pubs database. That is what I will use for this example.

Step 2: Unload the project

We will need to add a entry to the project file to import the MSBuild .targets file for Power Tools. In order to do this you have to unload the project so that you can edit project file using the XML editor. Right click on the project node inside the Solution Explorer and choose "Unload Project".

Step 3: Import the Microsoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.targets file

The next step is to modify the project file to add an import statement to include the Microsoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.targets file which contains the MSBuild task for T-SQL Static Code Analysis. Right click on the unloaded project in Solution Explorer and choose "Edit <project name.dbproj>".

This will open the .dbproj file inside the Visual Studio XML editor. Now we need to add an import statement to load the .target file. If you search for "<Import" inside the project file you will find the regular import which loads the normal MSBuild tasks for the database project like the SqlBuildTask and the SqlDeployTask.

    1: <!--Import the settings-->

    2: <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.VisualStudio.TeamSystem.Data.Tasks.targets" />

We need to add a reference to the Power Tools targets file.

    1: <!--Import the settings-->

    2: <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.VisualStudio.TeamSystem.Data.Tasks.targets" />

    3: <Import Project="$(MSBuildExtensionsPath)\Microsoft\VisualStudio\v9.0\TeamData\Microsoft.VisualStudio.TeamSystem.Data.PowerTools.Tasks.targets" />

Step 4: Reload the project

After you made the project change, including the new targets file, it is time to reload the project. Right click on the unloaded project node inside Solution Explorer and choose "Reload Project". This will reload the project.

NOTE: Make sure you have no errors when loading the project, because this will be an indication that your edits were not correct.

Step 5: Start a "Visual Studio 2008 Command Prompt"

The next step is to run T-SQL Static Code Analysis from the command line, in order to do so we need a command prompt that has the PATH set correctly so we can call MSBuild.exe. Visual Studio creates a shortcut in the menu structure that will launch a command prompt with the correct settings.

When you started the command prompt navigate to the directory where you project file is located that you changed in the previous step.

Step 6: Run T-SQL Static Code Analysis from the command-line

From the command prompt run: msbuild pubs.dbproj /t:SqlAnalysis

NOTE: The results file is created in the output directory, which by default is the SQL directory

Step 7: Look at the results

When executing the project using the SqlAnalysis target, an XML file is created that contains the results, named StaticCodeAnalysis.Results.xml. This file contains the information that is normally displayed in the Visual Studio "Error List".

When you open the file in VS it will look like this:

    1: <?xml version="1.0" encoding="utf-16" standalone="no"?>

    2: <Problems>

    3:   <Problem>

    4:     <Rule>Microsoft.Design#SR0010</Rule>

    5:     <ProblemDescription>You should avoid using the following join syntaxes, which have been deprecated: Table1.Id = Table2.Id, Table1.Id *= Table2.Id, Table1.Id =* Table2.Id. You should use the newer syntax, which uses the JOIN keyword.</ProblemDescription>

    6:     <SourceFile>c:\work\pubs\Schema Objects\Stored Procedures\dbo.reptq2.proc.sql</SourceFile>

    7:     <Line>4</Line>

    8:     <Column>14</Column>

    9:     <Severity>Warning</Severity>

   10:   </Problem>

   11:   <Problem>

   12:     <Rule>Microsoft.Design#SR0010</Rule>

   13:     <ProblemDescription>You should avoid using the following join syntaxes, which have been deprecated: Table1.Id = Table2.Id, Table1.Id *= Table2.Id, Table1.Id =* Table2.Id. You should use the newer syntax, which uses the JOIN keyword.</ProblemDescription>

   14:     <SourceFile>c:\work\pubs\Schema Objects\Stored Procedures\dbo.reptq2.proc.sql</SourceFile>

   15:     <Line>4</Line>

   16:     <Column>23</Column>

   17:     <Severity>Warning</Severity>

   18:   </Problem>

   19:   <Problem>

   20:     <Rule>Microsoft.Design#SR0010</Rule>

   21:     <ProblemDescription>You should avoid using the following join syntaxes, which have been deprecated: Table1.Id = Table2.Id, Table1.Id *= Table2.Id, Table1.Id =* Table2.Id. You should use the newer syntax, which uses the JOIN keyword.</ProblemDescription>

   22:     <SourceFile>c:\work\pubs\Schema Objects\Views\dbo.titleview.view.sql</SourceFile>

   23:     <Line>4</Line>

   24:     <Column>15</Column>

   25:     <Severity>Warning</Severity>

   26:   </Problem>

   27:   <Problem>

   28:     <Rule>Microsoft.Design#SR0010</Rule>

   29:     <ProblemDescription>You should avoid using the following join syntaxes, which have been deprecated: Table1.Id = Table2.Id, Table1.Id *= Table2.Id, Table1.Id =* Table2.Id. You should use the newer syntax, which uses the JOIN keyword.</ProblemDescription>

   30:     <SourceFile>c:\work\pubs\Schema Objects\Views\dbo.titleview.view.sql</SourceFile>

   31:     <Line>4</Line>

   32:     <Column>23</Column>

   33:     <Severity>Warning</Severity>

   34:   </Problem>

   35:   <Problem>

   36:     <Rule>Microsoft.Performance#SR0005</Rule>

   37:     <ProblemDescription>You might cause a table scan when you use an expression that starts with "%" in the LIKE predicate. </ProblemDescription>

   38:     <SourceFile>c:\work\pubs\Schema Objects\Stored Procedures\dbo.reptq3.proc.sql</SourceFile>

   39:     <Line>6</Line>

   40:     <Column>73</Column>

   41:     <Severity>Warning</Severity>

   42:   </Problem>

   43:   <Problem>

   44:     <Rule>Microsoft.Performance#SR0007</Rule>

   45:     <ProblemDescription>You have specified a nullable column that is not wrapped with the ISNULL function. This practice can cause the final result to be evaluted as NULL for the predicate.</ProblemDescription>

   46:     <SourceFile>c:\work\pubs\Schema Objects\Stored Procedures\dbo.byroyalty.proc.sql</SourceFile>

   47:     <Line>4</Line>

   48:     <Column>7</Column>

   49:     <Severity>Warning</Severity>

   50:   </Problem>

   51:   <Problem>

   52:     <Rule>Microsoft.Performance#SR0007</Rule>

   53:     <ProblemDescription>You have specified a nullable column that is not wrapped with the ISNULL function. This practice can cause the final result to be evaluted as NULL for the predicate.</ProblemDescription>

   54:     <SourceFile>c:\work\pubs\Schema Objects\Stored Procedures\dbo.reptq3.proc.sql</SourceFile>

   55:     <Line>6</Line>

   56:     <Column>7</Column>

   57:     <Severity>Warning</Severity>

   58:   </Problem>

   59:   <Problem>

   60:     <Rule>Microsoft.Performance#SR0007</Rule>

   61:     <ProblemDescription>You have specified a nullable column that is not wrapped with the ISNULL function. This practice can cause the final result to be evaluted as NULL for the predicate.</ProblemDescription>

   62:     <SourceFile>c:\work\pubs\Schema Objects\Stored Procedures\dbo.reptq3.proc.sql</SourceFile>

   63:     <Line>6</Line>

   64:     <Column>27</Column>

   65:     <Severity>Warning</Severity>

   66:   </Problem>

   67: </Problems>

Step 8: Clean up the results

If you want to clean up the resulting file produced by the static code analysis run, simply run:

msbuild pubs.dbproj /t:ScaClean

This will remove the results file.

Conclusion:

Following the steps outlined before enable you to run T-SQL Static Code Analysis from the command line, you can use the same step to integrate this in to your Team Build environment, but you have to make sure that you install the Power Tools on the build server before you can use this.

-GertD
"DataDude" Group Engineering Manager