Share via

How to: Analyze Transact-SQL Code to Find Defects

  • Article

You can analyze your Transact-SQL (T-SQL) code on demand, when you build your project by using the user interface, or when you build your project by using MSBuild. After your code is analyzed, one or more errors or warnings will appear if the code might contain design, naming, or performance defects. You can click these errors or warnings to view the code to which they relate. You can also view more information to better understand their causes and whether you might want to suppress them. For more information about code analysis in Visual Studio Team System Database Edition, see Improving Database Code with Static Analysis.

Before you analyze your code, you might want to configure the rules that will run as part of the analysis. For more information, see How to: Enable and Disable Specific Rules for Static Analysis of Database Code. You can configure different sets of rules for different build configurations. You could have your developers run a smaller set of rules to check for common problems before they check in their code, and you can run a more-comprehensive analysis on a nightly basis.

Analyzing Code in Database Edition

To analyze the T-SQL code in your database project

  1. Open the solution that contains your database project.

  2. On the Data menu, point to Static Code Analysis, and click Run.

    The code in your database project is analyzed, and any warnings and errors appear in the Error List.

To view the source code that caused an error or warning

  1. In the Error List, double-click an error or warning. You can also highlight an error or warning and press ENTER.

    The T-SQL editor opens and displays the code to which the error or warning relates. When possible, the cursor is positioned at the clause that caused the error or warning.

  2. Perform one of the following steps:

To display more information about an error or warning

  • In the Error List, perform one of the following steps:

    • Click the error or warning, and press F1.

    • Right-click the error or warning, and click Show Error Help.

      The Help topic that is associated with the error or warning appears. The topic contains information about the cause of the issue, how you can correct your code, and whether you might want to suppress the error or warning.

Analyzing Code by Using MSBuild

You might decide to analyze your code by using MSBuild if you want to apply a more-comprehensive set of rules during a nightly run. You can configure rules for each build configuration and then specify the configuration that contains the rule or rules that you want to apply.

You can use a response file to provide command-line arguments to MSBuild. This file can contain any command-line switches that are valid for MSBuild, including those that override project properties. For more information about response files, see MSBuild Response Files. For more information about command-line options for MSBuild, see MSBuild Command Line Reference.

When you analyze your code from a command prompt, the results are written to an XML file that is named StaticCodeAnalysis.Results.xml. You can find this file in your project folder in the SQL subfolder. You can specify a different file name by overriding the value of the ResultsFile project property.

To analyze the T-SQL code in your database project from a command prompt

  1. Open a Command Prompt window.

    Important noteImportant Note:

    To run MSBuild.exe, you must use the Visual Studio Command Prompt, or you must run the vsvars32.bat batch file in the folder that the %VS80COMNTOOLS% environment variable specifies.

  2. Browse to your project directory.

  3. At a command prompt, type a command line that resembles the following example:

    MSBuild /t:StaticCodeAnalysis /p:Configuration=Release DatabaseProject.dbproj

    Note

    MSBuild.exe must be on the path that your PATH environment variable specifies.

    Output such as the following will appear:

Microsoft (R) Build Engine Version 3.5.30718.1
[Microsoft .NET Framework, Version 2.0.50727.3051]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 9/11/2008 3:38:29 PM.
Project "C:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\DatabaseProject\DatabaseProject.dbproj" on node 0 (StaticCodeAnalysis target(s)).
  Creating a model to represent the project...
  Loading project files...
  Building the project model and resolving object interdependencies...
  Validating the project model...
C:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\DatabaseProject\SCHEMA OBJECTS\SCHEMAS\DBO\PROGRAMMABILITY\STORED PROCEDURES\sp_ProcedureName.proc.SQL(1,24): warning : SR0016 : Microsoft.Naming : Stored procedure(sp_ProcedureName) includes sp_ prefix in its name.
  One problem has been detected.
  The results are saved in StaticCodeAnalysis.Results.xml.
Done Building Project "C:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\DatabaseProject\DatabaseProject.dbproj" (StaticCodeAnalysis target(s)) -- FAILED.


Build FAILED.

"C:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\DatabaseProject\DatabaseProject.dbproj" (StaticCodeAnalysis target) (1) ->
(DspStaticCodeAnalysis target) ->
  C:\Documents and Settings\UserName\My Documents\Visual Studio 2008\Projects\SolutionName\DatabaseProject\SCHEMA OBJECTS\SCHEMAS\DBO\PROGRAMMABILITY\STORED PROCEDURES\sp_ProcedureName.PROC.SQL(1,24): warning : SR0016 : Microsoft.Naming : Stored procedure(sp_ProcedureName) includes sp_ prefix in its name.

    1 Warning(s)
    0 Error(s)

Time Elapsed 00:00:07.07

See Also

Tasks

Walkthrough: Analyze Transact-SQL Code for an Existing Database

Concepts

Improving Database Code with Static Analysis

Other Resources

Transact-SQL Design Issues

Transact-SQL Naming Issues

Transact-SQL Performance Issues

Detecting and Correcting Managed Code Defects