Debugging SQL in SSMS v18.0+

Question

I've just installed SSMS v18.6 and surprisingly I noticed that there's no debug menu anymore! I'm very dependent on debugging my SQL scripts like stored procs. Can anyone explain how I can achieve this? Any help would be much appreciated.

Notorious

From dba.stackexchange.com : It's been removed. You'll have to use SSDT instead. It simply wasn't used by developers. In 20+ years of building data warehouse and reporting databases I've never used the debugger. If I had to use such complex logic, a proper programming language would be far better. If I really had to use complex logic I could use SQLCLR but I haven't used that either. – Caboodle

From the linked answers, and the comments in Brent Ozar's article about SSMS 18 you'll see that very few people actually used the debugger. SQL is about querying, not long procedures, and the debugger can't help with that at all. SSMS 18 removed diagrams too, but brought them back after the outcry. With the debugger, there weren't enough complains to bring it back – Caboodle

Does this answer your question? How do you debug or step through the code in SQL Server Management Studio 18? – Weiler

Answer 1

Debugging capabilities are no longer available in SSMS v18.0 onwards. I guess it's not been in high demand and also because of the overload it has imposed on the software. If you still need to debug your SQL scripts you can do it by using Visual Studio. There is a good video at How to debug SQL code showing you how to achieve your goal.

Hako

I don't know anyone who thinks VS debugger is better than the SSMS debugger. Imo the decision shows MS was completely out of touch with real users. – Illative

Answer 2

You can debug the SQL Server stored procedure and scripts with Visual Studio.NET,

1. Open the Visual Studio.NET and click the continue without code (link)

2. Connect to SQLServer in Visual Studio.NET. Menu Tools->SQL Server->New Query

3. Enter Sql Server Connection Data

4. In script note write your script or Invoke the Stored procedure or User define function

5. F9 or double click left of code line for out the break point

6. In menu SQL-> Execute with debugger

7. with F11 trace line By line

8. Complete

Picaresque

Answer 3

Sometimes you can debug using and older version of SSMS (like 2014) that still has the debug feature build in. I haven't tested though for what newer SQL Server versions this still works. I'm having problem using Visual Studio for debugging if there are nested stored procedures. It starts asking for the source-code when it returns to the caller procedure. So unfortunately they took away debugging and in return we got an alternative that has bugs.

Twist

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Diphase