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 panel in the SSMS, on the left side of the query window.

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

When the Code structure is enabled, it will open its own panel 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 panel, find that block and double-click on it:

As it’s shown on the picture above, with a double-click on the particular code block, in this case Create view vbooks 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 the code for the specific trigger needs to be seen, in this case Create trigger HumanResources.dEmployee, and when 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, if some part of the code is missing it can be seen in the Code structure panel. When that missing part of the code is added, it will be automatically added to the Code structure panel, 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)) 
AS
BEGIN
DECLARE @last_child hierarchyid
DECLARE @last_child2 hierarchyid
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION 

UPDATE Org_T5 
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)
COMMIT
END ;
GO

This will automatically add Create procedure AddEmp5 to the Code structure panel, 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 with a click on two icons in the top left corner of the Code structure window.