How to preview SQL Server code structure and control flow

In this article, we’ll introduce the Code structure feature in ApexSQL Complete. ApexSQL Complete is a productivity add-in for SSMS and Visual Studio, that features SQL auto-complete, SQL formatting, and more. The Code structure feature allows the user to easily view and find all important code blocks, and to review the SQL code structure of the query.

The Code structure provides a tree-like form of the SQL code from the query, and it’s presented in its own window in the SSMS, on the left side of the query window.

The SQL Code structure feature can be enabled from the ApexSQL Complete menu, by clicking on the Code structure command, or by activating it with the shortcut Ctrl+Q Ctrl+W:

When the Code structure is enabled it will open its own window beside the query window. In the Code structure window, all important parts of the code can be seen, which are used in the query:

To navigate to a particular code block in the query, scroll up/down in the Code structure window, find that block, and double-click on it:

As it’s shown on the picture above, by double-clicking on the particular code block, in this case ALTER PROCEDURE uspGetEmployeeManagersit will find that part of the code in the query window, center it, and highlight the entire code block.

The Code structure feature also provides the ability to expand the code, and navigate to the part which is under the main code:

ApexSQL Complete’s Code structure feature is particularly helpful with the large scripts. By using this feature, the user can easily navigate through the most important parts of the code, instead of having to scroll down the entire query.

If the script has more than 10,000 lines of code, by enabling the SQL Code structure feature, the code can be browsed and only the significant parts can be looked, which have a direct impact on the behavior of the script. For example, if you want to see the code for the specific trigger, in this case CREATE TRIGGER HumanResources.dEmployee, and when you navigate to it, it can be also expanded, and the other related code, which defines the trigger behavior can be seen:

When the script is created, you can look at the Code structure, and see if some part of the code is missing. When that missing part of the code is added, it will be automatically added to the Code structure, by default.

If the following code is inserted in the query, while the Code structure is enabled:

CREATE PROCEDURE AddEmp5(@mgrid hierarchyid, @EmpName nvarchar(50)) 
DECLARE @last_child hierarchyid
DECLARE @last_child2 hierarchyid

SET @last_child = LastChild = EmployeeId.GetDescendant(LastChild,NULL),
    @last_child2 = LastChild2 = EmployeeId.GetDescendant(LastChild2,NULL)
WHERE EmployeeId = @mgrid
INSERT Org_T5(EmployeeId, EmployeeId2, EmployeeName) 
    VALUES(@last_child, @last_child, @EmpName)

This will automatically add CREATE PROCEDURE AddEmp5 to the Code structure, and all related code can be expanded, as it’s shown on the picture below:

This option can be changed, so that the Code structure will be automatically refreshed, or manually refreshed.

Changing to automatically, or manually refresh, can be done by clicking on two icons in the top left corner of the Code structure window.


March 4, 2015