Walkthrough: Analyze Transact-SQL Code for an Existing Database

Article
08/23/2010

You can improve the quality of the Transact-SQL (T-SQL) code in a database schema by importing it into a database project and analyzing the code against a set of rules. For example, you might want to find any errors in a schema that you must use but that you did not develop and whose quality has not been verified. For more information, see Improving Database Code with Static Analysis.

Before you can analyze your database code, you must have already imported the schema for an existing database into a database project. That project contains code of an unknown level of quality. You want to analyze the Transact-SQL (T-SQL) code, applying all available rules for static code analysis. Later, you might decide to turn off some rules for your team, but for this initial assessment, you want to find all the potential problems in the database code. You review the warnings and the code that caused those warnings. You correct a warning, suppress more warnings, and then re-analyze the database project.

Prerequisites

Before you can complete this walkthrough, you must complete Walkthrough: Put an Existing Database Schema Under Version Control. In that walkthrough, you create a solution that contains a database project that is named MyNorthwind.

To configure rules for analyzing your database project

In Visual Studio, open the MyNorthwind solution.
In Schema View, expand the node for the database project if it is not already expanded.
On the Data menu, point to Static Code Analysis, and click Configure.

The code analysis properties for your database project appear.
In the Rules list, expand the Design, Naming, and Performance nodes to display all the rules that are available to analyze T-SQL code.
Verify that the check boxes for all rules are selected.

You can select or clear the check box for a category of rules, such as Design, to select or clear the check boxes for each rule in that category.

Note

You can treat a rule as an error instead of a warning by selecting the Treat Warning as Error check box for that rule.
On the File menu, click Save All.

Next, you will analyze the T-SQL code in your database project. In this walkthrough, you will start the analysis manually, but you can configure it to start after each successful build of your database project. For more information, see How to: Enable and Disable Static Analysis for Database Code.

To analyze your database project

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

The T-SQL code in your database project is analyzed, and warnings appear in the Error List. If the Error List does not appear, open the View menu, and click Error List.

Next, you will view and correct one of the warnings.

To view and correct a warning

In the Error List, find the following warning:

Microsoft.Designing#SR0010: Old-style JOIN syntax is used

The code that caused this warning is in the file that is named "dbo.CustOrderHist.proc.sql". You can find the code in line 4, columns 18, 38, and 48.
Right-click the warning in the Error List, and click Show Error Help.

The Help topic for rule SR0010 appears. You can learn about what triggered the rule, how to resolve the warning, and when you might want to ignore the warning. You can also see an example of T-SQL code that would cause this warning and updates to that code that would resolve the warning.
In the Error List, double-click the warning, or highlight it and press ENTER.

The T-SQL editor opens and displays the code that caused the warning (dbo.CustOrderHist.proc.sql). The cursor appears at the start of the code that caused the warning. In this case, the cursor appears in the FROM clause because the deprecated JOIN syntax is used to join the Orders, Order Details, and Products tables. The following code appears:
```
CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
GROUP BY ProductName
```

Update the code to match the following example:

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
SELECT ProductName, Total=SUM(Quantity)
FROM Products P
INNER JOIN [Order Details] OD
  ON P.ProductID = OD.ProductID
INNER JOIN [Orders] O
  ON OD.OrderID = O.OrderID
INNER JOIN [Customers] C
  ON O.CustomerID = C.CustomerID
WHERE C.CustomerID = @CustomerID
GROUP BY ProductName

On the File menu, click Save dbo.CustOrderHist.proc.sql.

Next, you will review and suppress a second warning.

To view and suppress a code analysis warning

In the Error List, find the following warning:

Microsoft.Naming#SR0011: Object name(Employee Sales by Country) contains special characters.

The code that causes this warning is in the file that is named "dbo.Employee Sales by Country.proc.sql". You can find the code in line 1, column 18.

At this point, you must decide whether to eliminate the special characters from the name of the object. By using database refactoring, you can automate updating all references to this object so that they contain the correct name. However, you will break any applications that depend on the old name. If you do not have enough information to decide on the best approach, you can suppress the warning until you have investigated the ramifications of the change. You might also create a work item in Visual Studio Team System Team Foundation Server to track this task or even assign it to someone else.
In the Error List, click the heading for the Description column.

The Error List sorts the warnings by their descriptions, grouping all the Microsoft.Naming#SR0011 warnings together.
Scroll the Error List until the first Microsoft.Naming#SR0011 warning (for the file "dbo.Alphabetical list of products.view.sql") appears, and then highlight all SR0011 warnings.

You can highlight a contiguous list of warnings by clicking the first warning in the list and then pressing the SHIFT key as you click the last warning in the list.

Note

You can create a work item from this list by right-clicking any highlighted row, pointing to Create Work Item, and clicking a type of work item. If you suppress the warning until the name can be changed, you should include an instruction in the work item to unsuppress the warning after the name is changed. To keep this walkthrough brief, you will skip creating the work items.
Right-click any highlighted row, and click Suppress Static Code Analysis Message(s).

A file that is named StaticCodeAnalysis.SuppressMessages.xml is added to your database project. The MyNorthwind.dbproj file is checked out from version control. The suppressed warnings disappear from the Error List, leaving fewer warnings in the list.

Note

If you suppress a warning for a file in your database project, you suppress all instances of that warning for that file.

The XML file contains the list of suppressed warnings. You can delete that file if you want to unsuppress all suppressed warnings. For more information, see How to: Stop Suppressing Warnings for Database Code Analysis.

In the final procedure, you will re-analyze the database project.

To re-analyze your database project

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

The T-SQL code in your database project is analyzed again, and 28 warnings appear in the Error List. The warnings that you corrected or suppressed do not appear.

Next Steps

In a typical environment, you would analyze each warning that appeared in the Error List. You would then fix the issues that you can fix immediately, suppress the issues that can be ignored, and create work items for issues that must be fixed later. For some of the issues, you can use database refactoring to resolve the warnings. For more information about database refactoring, see Refactor Database Code and Data.

After you resolve or suppress each warning, you should run your database unit tests and application tests to verify that the changes have not introduced problems. For more information about database unit tests, see Verifying Existing Database Code with Unit Tests.

Share via