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

Question

Can we see the values (rows and cells) in a table valued variable in SQL Server Management Studio (SSMS) during debug time? If yes, how?

Feckless

Answer 1

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

Overview

Same answer in 2012 – Fatso

And here we are in 2016. No better. The Connect link also doesn't work anymore. – Whish

To be honest this is not actually an answer as it does not answer the question, the question is how to do it (not whether SSMS has any feature for it), stating this is not implemented while there are ways to display the values is not very helpful. – Extra

Please delete this answer. The answer below should be the accepted answer. – Rubberneck

2018, still nope. – Hyacinthe

From Microsoft: "Please use Debugging feature in SSDT. TSQL Debugging has been deprecated for now." (source: feedback.azure.com/forums/908035-sql-server/suggestions/…) – Ramadan

This does not answer the question. The Question is how to do it. – Mcshane

Stating that a probable feature is not yet implemented cannot be considered an answer, since it is not useful. Thanks anyway for your time. – Gomez

Answer 2

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.

Minima

This is definitely a work around that is good enough for small tables to be presented as XML. – Feckless

But still don't stop reading! Another awesome option below! Great thread!! – Clementinaclementine

I have had large tables with the XML not very friendly to read. I take another step - copy the XML and paste in xmlgrid.net and you can see the XML as a table. The table visualization really helps. Waiting for a release of SSMS that has a table viewer like the datatable viewer in Visual Studio. – Instructions

You can add ",ROOT('rootNodeName')" to the "FOR XML" clause. This will collect multiple rows (if any) under a single root, which makes for a legal XML document, which can be viewed with the XML visualizer instead of the text visualizer. – Gorey

I can confirm that this also works for temp tables (SQL Server 2008 and higher). – Nsf

Using SQL Server 2016 or never you can also use JSON version of this DECLARE @v nvarchar(max) = (SELECT * FROM <tablename> FOR JSON AUTO) – Dacy

The JSON version mentioned by @Dacy produces a much more readable result than the XML version, it should be added as an independent answer for better visibility. – Unstoppable

Answer 3

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.

Jiujitsu

Great for temp tables, but not for table variables – Hackery

Also hangs if there is an open transaction, so not much use if using a debugging a test in a framework such as tSQLt which always opens a transaction at the start of the test. – Lido

WOW WOW WOW WOW – Comprise

Good - would be great if you could sort by a single column or two – Rampageous

Absolutely fantastic solution, thanks for the share! – Sharp

Answer 4

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"

Rainmaker

Answer 5

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)

Dacy

For people (like me) who are not familiar with JSON Visualizers: Next to the variable's 'Value' in the Locals or Watch window there's a pulldown. Select JSON to get the window shown here. – Palmette

Answer 6

I have come to the conclusion that this is not possible without any plugins.

Feckless

I saw some previews of Visual Studio 2010. Very fancy, complex, and detailed... and the demonstrator did not know if temp tables could be viewed in debug mode. Maybe when it's released, but I'm not holding my breath. – Merge

Answer 7

SQL Server Profiler 2014 lists the content of table value parameter. Might work in previous versions too. Enable SP:Starting or RPC:Completed event in Stored Procedures group and TextData column and when you click on entry in log you'll have the insert statements for table variable. You can then copy the text and run in Management Studio.

Sample output:

declare @p1 dbo.TableType
insert into @p1 values(N'A',N'B')
insert into @p1 values(N'C',N'D')

exec uspWhatever @PARAM=@p1

Espionage

Answer 8

Why not just select the Table and view the variable that way?

SELECT * FROM @d

Motorway

Please explain how to go about that. Adding a SELECT * FROM @Table to the script being debugged does not emit results to the results window. – Roil

The T-SQL debugging tooling has been removed from Management Studio. One reason is likely that people who write a lot of T-SQL simply don't use it, mainly because it doesn't let you execute SQL at the breakpoint. Instead I copy the proc and either make a new proc or just a batch and add debugging selects to that code. Sounds clunky, but its been the best way for hundreds of scenarios for me. – Lougheed

Answer 9

Sorry guys, I'm a little late to the party but for anyone that stumbles across this question at a later date, I've found the easiest way to do this in a stored procedure is to:

1. Create a new query with any procedure parameters declared and initialised at the top.
2. Paste in the body of your procedure.
3. Add a good old fashioned select query immediately after your table variable is initialised with data.
4. If 3. is not the last statement in the procedure, set a breakpoint on the same line, start debugging and continue straight to your breakpoint.
5. Profit!!

messi19's answer should be the accepted one IMHO, since it is simpler than mine and does the job most of the time, but if you're like me and have a table variable inside a loop that you want to inspect, this does the job nicely without too much effort or external SSMS plugins.

Everick

This is completely opposite of what the OP is trying to do. – Downtime