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?

enter image description here

score 330 · Answer 1 · edited Feb 16 '15 at 13:43

330

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.

enter image description here

edited Feb 16 '15 at 13:43

Faiz

5,331
10
45
57

answered Jul 19 '11 at 14:13

kirby

3,301
2
14
2

1

This is definitely a work around that is good enough for small tables to be presented as XML. – Faiz Feb 16 '15 at 12:38
4

But still don't stop reading! Another awesome option below! Great thread!! – Mike M May 25 '15 at 12:33
1

I have had large tables with the XML not very friendly to read. I take another step - copy the XML and paste in http://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. – Moiz Tankiwala Feb 21 '17 at 01:19
1

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. – JohnL4 Sep 25 '17 at 18:53
I can confirm that this also works for temp tables (SQL Server 2008 and higher). – Jeff Mergler Jul 03 '18 at 15:35
5

Using SQL Server 2016 or never you can also use JSON version of this `DECLARE @v nvarchar(max) = (SELECT * FROM FOR JSON AUTO)` – Sousuke Dec 21 '18 at 12:16
1

The JSON version mentioned by @Sousuke produces a much more readable result than the XML version, it should be added as an independent answer for better visibility. – Martin Riddar Mar 31 '20 at 12:34

score 32 · Accepted Answer · answered Oct 21 '10 at 11:44

32

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

answered Oct 21 '10 at 11:44

rortega

534
4
4

4

[Same answer in 2012](http://stackoverflow.com/questions/17610446/how-to-view-data-in-table-variables-during-debugging-session-in-ms-sql-managemen) – qdev76 Feb 19 '14 at 16:02
2

And here we are in 2016. No better. The Connect link also doesn't work anymore. – dotNET Apr 27 '16 at 03:50
6

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. – Răzvan Flavius Panda Nov 21 '16 at 11:15
2

Please delete this answer. The answer below should be the accepted answer. – Vortex852456 Aug 03 '17 at 08:07
2018, still nope. – AgentFire Feb 25 '18 at 20:27
From Microsoft: "Please use Debugging feature in SSDT. TSQL Debugging has been deprecated for now." (source: https://feedback.azure.com/forums/908035-sql-server/suggestions/32902687-ssms-debugger-should-show-the-contents-of-tsql-tab) – bart Mar 20 '19 at 02:01
This does not answer the question. The Question is how to do it. – user3048027 May 29 '20 at 14:59
Stating that a probable feature is not yet implemented cannot be considered an answer, since it is not useful. Thanks anyway for your time. – alejandrob Dec 02 '21 at 13:58

score 20 · Answer 3 · answered Nov 08 '11 at 21:35

20

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.

answered Nov 08 '11 at 21:35

James Hulse

1,566
15
32

7

Great for temp tables, but not for table variables – Harag May 08 '14 at 09:48
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. – Nathan Jan 24 '18 at 21:37
1

WOW WOW WOW WOW – vothaison Jul 26 '18 at 03:50
Good - would be great if you could sort by a single column or two – user2486488 Dec 12 '19 at 11:56
Absolutely fantastic solution, thanks for the share! – B.M. Oct 13 '21 at 10:51

score 7 · Answer 4 · answered Feb 10 '15 at 15:02

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"

score 6 · Answer 5 · answered Apr 07 '20 at 12:05

6

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)

answered Apr 07 '20 at 12:05

Sousuke

1,203
1
15
25

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. – David Wohlferd May 30 '21 at 21:11

score 0 · Answer 6 · answered Jan 06 '10 at 17:21

0

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

answered Jan 06 '10 at 17:21

Faiz

5,331
10
45
57

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. – Philip Kelley Jan 06 '10 at 17:25

score 0 · Answer 7 · answered Aug 21 '14 at 15:48

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

score -2 · Answer 8 · edited Mar 14 '16 at 20:17

-2

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

SELECT * FROM @d

edited Mar 14 '16 at 20:17

zero323

322,348
103
959
935

answered Mar 14 '16 at 19:59

Kyle Hoener

13
1

6

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. – StingyJack Apr 10 '18 at 17:32
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. – R.M. Buda Apr 29 '23 at 08:41

score -2 · Answer 9 · answered May 13 '16 at 01:45

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:

Create a new query with any procedure parameters declared and initialised at the top.
Paste in the body of your procedure.
Add a good old fashioned select query immediately after your table variable is initialised with data.
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.
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.

This is completely opposite of what the OP is trying to do. – Jamie Marshall Jun 21 '18 at 16:34 — Jamie Marshall, Jun 21 '18 at 16:34

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

9 Answers9

Linked