The TSQL debugger was in SQL Server 2000 tools and will be in SQL Server 2008 tools. However, it’s not built in to SQL Server 2005 tools for TSQL stored procedures. There is a built in debugger that ships with SQL Server for CLR, SSIS (BIDS), and MDX (calculations only, in BIDS) but nothing for TSQL in SSMS or BIDS. However, you can use Visual Studio (BIDS is a stub of VS but you need the full install of VS for debugging).

 

·         How to: Use Breakpoints With Stored Procedures, User-Defined Functions, and Triggers http://msdn.microsoft.com/en-us/library/xehsh1hb(VS.80).aspx

o   Open Visual Studio 2005 and open a database project (I used C#).

o   Go to tools.connect to database and choose Microsoft SQL Server as the data source.

o   Choose your development server and database then click the “Test Connection” button to verify you can connect then choose OK.

o   Under data connections in the Serve Explorer, expand your connection then expand “Stored Procedures”.

o   Right click on your storproc and choose Open.

o   Add a breakpoint by clicking on the grey bar to the left of any line of code or right-click on a line of code and choose Breakpoint.Insert Breakpoint.

o   Hit F5, the green arrow, or from the menu Debug.Start Debugging to step into the storproc. Use F5/F10 to step into or over the breakpoints.

·         This blog talks about using Visual Studio 2005 to debug both TSQL and CLR http://blogs.msdn.com/sqlclr/archive/2006/07/07/659332.aspx

·         Visual Studio 2008 can be used to debug SQL Server 2005 stored procedures http://msdn.microsoft.com/en-us/library/kkyhd4yb.aspx

 

For SQL Server 2008: