How to See the Values of a Table Variable at Debug Time in T-Sql

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

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

How to view data in table variables during debugging session in MS SQL Management Studio 2012?

Whilst I can't find any documetation, anywhere, that explicitly states that you cannot inspect table variables, I don't believe that it's possible. From Transact-SQL Debugger

Locals and Watch. These windows display currently allocated Transact-SQL expressions. Expressions are Transact-SQL clauses that evaluate to a single, scalar expression. The Transact-SQL debugger supports viewing expressions that reference Transact-SQL variables, parameters, or the built-in functions that have names that start with @@. These windows also display the data values that are currently assigned to the expressions.

(My emphasis)

That is, you can only inspect scalars.

As to your attempt to use the Immediate window, the Limitations on Debugger Command and Features says:

The Immediate window is displayed, but you cannot do anything useful with it, such as setting a variable to a value, or querying the database.

---

I've never really used the debugger much - everytime I've looked into it, I encounter limitations like this.

That's why I still tend to use "old-skool"/"printf" approaches to debug SQL - include extra SELECT *s liberally throughout the code showing the current state of tables, and extra PRINT or RAISERROR messages that show other states, etc. And then just run the code normally, until you've bashed it into shape.

Viewing contents of table variable when debugging in sql server 2012

No, I'm sorry to report that this is still not possible in SQL Server 2012. The feature has been requested, received a lot of upvotes, but was discarded after four years of deliberation:

http://connect.microsoft.com/SQL/feedback/details/363054

How do I inspect table variables and temporary tables from within a debugging session in SSMS 2008?

I built a procedure which will display the content of a temp table from another database connection. (which is not possible with normal queries).
Note that it uses DBCC PAGE & the default trace to access the data so only use it for debugging purposes.

View temporary table`s data when debugging an MS SQL Function

One possible solution, that may not be the best, is to:

• Create a permanent table that is the same as the temporary table
• Modify the function so that it dumps the data from the temporary table into the permanent table at the point where the temp table contains the data you're interested in seeing

When the function ends, open up the new permanent table and you'll have a copy of the temporary table's state.

This requires that you have permission to create new tables and modify the function.

How to debug T-SQL with SQL Server Management Studio 2017?

You just need to download the Visual Studio 2019 Community.

Once you've done that, create a new project and open the SQL Server Object Explorer (CTRL + S).

You will be able to see your list of SQL Server databases, just as you did in SQL Server Management Studio.

Finally, left click one database and select "New Query". Now you can debug T-SQL just as you did in SSMS.

But the debugger does not work with Azure SQL

Query temp table in stored proc whilst debugging in SQL 2008 Management Studio

Use global temporary tables, i.e. with double hash.

insert into ##temp select ...

While debugging, you can pause the SP at some point, and in another query window, the ## table is available for querying.

select * from ##temp

Single hash tables (#tmp) is session specific and is only visible from the session.

---

Related Topics