How to debug a SQL script

Note

Starting with SSMS version 21, SQL Complete is not added to the menu bar by default. You can manually add it.

To add SQL Complete to the SSMS menu bar:

1. Navigate to Extensions and select Customize Menu.

2. In the Customize > Extensions Menu dialog that opens, clear the checkboxes next to SQL Complete and Debug.

3. Select Save and Restart.

4. In the confirmation dialog, select Yes to apply the changes.

To debug a SQL script:

1. Open the required script in the SQL editor.

2. Set a breakpoint for your statement by clicking in the gray margin next to the SET statement.

3. In the Debug menu, click Start.

If a statement is invalid, an error message appears in the error list, though the debugging process continues. The debugging of a SQL script can be suspended by clicking the Stop button. You can also use different step commands of the Debug menu to trace a SQL script.

To debug a SQL script, you don’t need any special privileges, except for the privileges, required for the execution of the script statements.

If a SQL script contains a call of another script with the EXECUTE command, you can use the Step Into command to step into the script being called. If this script contains breakpoints, execution stops on these breakpoints. The Call Stack window shows the stack of the script calls.

You can use the Watches window to view server and session variables during SQL script debugging.