25

I create a global temp table (i.e ##TheTable) using C# code. I want to be able to see that temp table in SQL server management studio after the code runs completely.

Is it possible to do this ? If yes, then how ?

Improve this question
4
  • 1
    But after the code runs completely the temp table is gone. That is why they call it temp. Commented Jan 29, 2014 at 19:33
  • paparazzo, not all temporary tables are removed after the code runs. Table variables are removed in this way, but temp tables are not automatically removed after the code runs. Commented Jan 28, 2022 at 17:47
  • @ZenoArrow right if you mean that it can still be there since the data flow is still open in some sort. If you debug in SSIS and you have not stopped the debug mode, then yes, the temporary table will still be there. But as soon as you stop the debug mode, the temporary table (also a ##tmpTable) will be gone. Commented Mar 25, 2024 at 20:11
  • 1
    @questionto42 Yes, when the temp tables are cleared (without explicitly deleting them) depends on when the session that created them is closed. For example, if you're working with a SQL Server database in SSMS, you could run SQL that creates a temp table that persists after the SQL has finished running, and it'll only be closed at the point when the user that created the temp tables had their database connection closed (e.g. by logging out of the SQL Server instance). Commented Mar 28, 2024 at 8:34

3 Answers 3

Reset to default
35

All temp tables are logged under SQL server > Databases > System Databases > tempdb -> Temporary Tables

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

SQL server > Databases > System Databases > tempdb -> Temporary Tables
I cannot see any temp table under the Temporary tables.
Try putting a breakpoint after the creation of the temp table, and then checking in SSMS. Don't forget to refresh the Temporary Tables folder as it does not update automatically. ##tables should be persisted even after the connection/session has dropped. #tables are only there for the session they are made in.
I tried it by NOT closing my connection and that did not work. I will try the breakpoint now.
Okay. The breakpoint works. But, why am I not able to see the temp table after the code (without breakpoints) has executed fully ? How to make it possible to still see the temp table without breakpoint ?
|
9

After the code has finished and the session is closed, the temporary table will cease to exist. If you want to see it in SQL Server Management Studio (SSMS), you need to keep the code session open until the data has been reviewed in SSMS.

Per Technet:

Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.

As an alternative, there's a C# code option here that selects the data from the temporary table into a code variable for review in the code ... (and if the code is to exist, you could possibly write it to a file or review by another means) -- see: https://stackoverflow.com/a/6748570/3063884

Improve this answer

Comments

6

Create a test table 

SELECT * INTO ##temp1
FROM dbo.SomeTable_Name

Now to check if table is there 

SELECT  * FROM tempdb.dbo.sysobjects O
WHERE O.xtype in ('U') 
AND O.ID = OBJECT_ID(N'tempdb..##temp1')
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.