How to see the values of a table variable at debug time in T-SQL?

Question

DECLARE @v XML = (SELECT * FROM <tablename> FOR XML AUTO)

Insert the above statement at the point where you want to view the table's contents. The table's contents will be rendered as XML in the locals window, or you can add @v to the watches window.

That's not yet implemented according this Microsoft Connect link: Microsoft Connect

This project https://github.com/FilipDeVos/sp_select has a stored procedure sp_select which allows for selecting from a temp table.

Usage:

exec sp_select 'tempDb..#myTempTable'

While debugging a stored procedure you can open a new tab and run this command to see the contents of the temp table.

In the Stored Procedure create a global temporary table ##temptable and write an insert query within your stored procedure which inserts the data in your table into this temporary table.

Once this is done you can check the content of the temporary table by opening a new query window. Just use "select * from ##temptable"

If you are using SQL Server 2016 or newer, you can also select it as JSON result and display it in JSON Visualizer, it's much easier to read it than in XML and allows you to filter results.

DECLARE @v nvarchar(max) = (SELECT * FROM Suppliers FOR JSON AUTO)