2

I'm stepping through a stored procedure in SQL Server Management Studio 2008 (SSMS). The code creates some table variables as well as temporary # tables that I would like to inspect as I go along. Now the other local variables I can see in the "Locals" window and while the table variables are listed there, I can't see their contents. I would also like to inspect the # temp tables but again any select statement that I want to run against them will need to come from the same session as the code that I'm stepping through.

Is this possible in the SSMS 2008 debugger?

snth
  • 5,194
  • 4
  • 39
  • 48
  • Dup of http://stackoverflow.com/questions/1900857/how-to-see-the-values-of-a-table-variable-at-debug-time-in-t-sql – Schultz9999 Sep 11 '12 at 21:16

3 Answers3

12

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.

Filip De Vos
  • 11,568
  • 1
  • 48
  • 60
  • 1
    Excellent procedure - is there a way to do the same thing for table variables e.g. select * from ATmytable ? – Harag May 08 '14 at 09:45
  • 1
    You can if you find the temp table name. See this answer for more info http://stackoverflow.com/a/8561729/619960 – Filip De Vos May 08 '14 at 09:49
  • I've used `select * From tempdb.sys.tables order by modify_date;` to get the latest local table, I've executed `exec dbo.sp_select 'tempdb..#AE512833'` but it returns: `The table [tempdb..#AE512833] does not exist`. Same with when I remove the `tempdb.` part. But I see the table name listed in the first query. Any ideas? :^) – user2173353 Aug 01 '17 at 09:05
  • You have to use the original temp table name, not the virtual name in tempdb. Your #AE512833 was created with a different name. This is the name you need to use. – Filip De Vos Aug 01 '17 at 12:17
1

What I do in a complex proc is add an input varaiable with a default value of 0 to the end of whatever variables I have called @test. (by doing it this way, I won't break any exisiting calls to the proc)

Then at each point where I might want to see the values of a table variable or temp table I put an if statement and I might even add a column so I know at what point I am looking at the table:

IF @test = 1 BEGIN SELECT 'after updating field 3' as TestStep, * FROM #temp END

Now they only run if running in test mode and I can see whatever I need to when debugging a problem two years from now. I also print any dynamic SQl statments I created in the test mode as well.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
1

This is largely the same question as this one: How to see the values of a table variable at debug time?

It appears that the short answer is: NO! This hasn't been implemented in SSMS 2008.

See this post on Microsoft Connect: SQL Debugging - All About Tables: Table Variables, #Temp, ##Global Temp, Source (Input) and Output Tables

Community
  • 1
  • 1
snth
  • 5,194
  • 4
  • 39
  • 48