How to: Analyze Transact-SQL Code to Find Defects
You can analyze your Transact-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 Premium and Visual Studio Ultimate, see Analyzing Database Code to Improve Code Quality.
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 Transact-SQL code in your database project
Open the solution that contains your database project.
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
In the Error List, double-click an error or warning. You can also highlight an error or warning and press ENTER.
The Transact-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.
Perform one of the following steps:
Fix the issue immediately.
Create a work item so that you or another team member will fix the issue later.
For more information, see How to: Create a Work Item from a Defect in Transact-SQL Code.
Suppress the error or warning if it can be safely ignored.
If you suppress an error or warning, it will not appear when you analyze your code again. For more information, see How to: Suppress Database Code Analysis Warnings.
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 Transact-SQL code in your database project from a command prompt
Open a Command Prompt window.
Important
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.
Browse to your project directory.
At a command prompt, type a command line that resembles the following example:
MSBuild /t:StaticCodeAnalysis /p:Configuration=Release DatabaseProject.dbprojNote
MSBuild.exe must be on the path that your PATH environment variable specifies.
Review the output from MSBuild.exe. Output such as the following will appear:
Microsoft (R) Build Engine Version 4.0.30115.0 [Microsoft .NET Framework, Version 4.0.30115.0] Copyright (C) Microsoft Corporation 2007. All rights reserved. Build started 9/11/2008 3:38:29 PM. Project " C:\Users\UserName\Documents\Visual Studio 2010\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... 1 problem has been detected C:\Users\UserName\Documents\Visual Studio 2010\Projects\SolutionName\DatabaseProject\SCHEMA OBJECTS\SCHEMAS\DBO\PROGRAMMABILITY\STORED PROCEDURES\sp_ProcedureName.proc.SQL(1,24): StaticCodeAnalysis warning : SR0014 : Microsoft.Rules.Data : Data loss might occur when casting from ASCII String(1) to NChar(1). [C:\Users\UserName\Documents\Visual Studio 2010\Projects\SolutionName\DatabaseProject\DatabaseProject.dbproj] One problem has been detected. The results are saved in C:\Users\UserName\Documents\Visual Studio 2010\Projects\SolutionName\DatabaseProject\sql\release\DatabaseProject.StaticCodeAnalysis.Results.xml. Done Building Project " C:\Users\UserName\Documents\Visual Studio 2010\Projects\SolutionName\DatabaseProject\DatabaseProject.dbproj" (StaticCodeAnalysis target(s)) -- FAILED. Build succeeded. " C:\Users\UserName\Documents\Visual Studio 2010\Projects\SolutionName\DatabaseProject\DatabaseProject.dbproj" (StaticCodeAnalysis target) (1) -> (DspStaticCodeAnalysis target) -> C:\Users\UserName\Documents\Visual Studio 2010\Projects\SolutionName\DatabaseProject\SCHEMA OBJECTS\SCHEMAS\DBO\PROGRAMMABILITY\STORED PROCEDURES\sp_ProcedureName.PROC.SQL(1,24): StaticCodeAnalysis warning : SR0014 : Microsoft.Rules.Data : Data loss might occur when casting from ASCII String(1) to NChar(1). [C:\Users\UserName\Documents\Visual Studio 2010\Projects\SolutionName\DatabaseProject\DatabaseProject.dbproj] 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
Analyzing Database Code to Improve Code Quality
Analyzing Managed Code Quality by Using Code Analysis