View Temporary Table Created from Stored Procedure

Question

I have a stored procedure in SQL 2005. The Stored Procedure is actually creating temporary tables in the beginning of SP and deleting it in the end. I am now debugging the SP in VS 2005. In between the SP i would want to know the contents into the temporary table. Can anybody help in in viewing the contents of the temporary table at run time.

Thanks Vinod T

Answer 1

Edit the stored procedure to temporarily select * from the temp tables (possibly into another table or file, or just to the output pane) as it runs..?

You can then change it back afterwards. If you can't mess with the original procedure, copy it and edit the copy.

Answer 2

There are several kinds of temporary tables, I think you could use the table which is not dropped after SP used it. Just make sure you don't call the same SP twice or you'll get an error trying to create an existing table. Or just drop the temp table after you see it's content. So instead of using a table variable (@table) just use #table or ##table

---

From http://techahead.wordpress.com/2007/09/27/sql-temporary-tables/:

Local Temporary Tables

• Local temporary tables prefix with single number sign (#) as the first character of their names, like (#table_name).
• Local temporary tables are visible only in the current session OR you can say that they are visible only to the current connection for the user. They are deleted when the user disconnects from instances of Microsoft SQL Server.

Global temporary tables

• Global temporary tables prefix with double number sign (##) as the first character of their names, like (##table_name).
• Global temporary tables are visible to all sessions OR you can say that they are visible to any user after they are created.
• They are deleted when all users referencing the table disconnect from Microsoft SQL Server.

Answer 3

This helped me.

SELECT * FROM #Name

USE [TEMPDB] GO

SELECT * FROM syscolumns WHERE id = ( SELECT id FROM sysobjects WHERE [Name] LIKE '#Name%')

this gives the details of all the temptable

Answer 4

To expand on previous suggestions that you drop the data into a permanent table, you could try the following:

-- Get rid of the table if it already exists
if object_id('TempData') is not null
  drop table TempData

select * into TempData from #TempTable