24

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 ?

CJBS
  • 15,147
  • 6
  • 86
  • 135
InTheSkies
  • 999
  • 2
  • 7
  • 13
  • 1
    But after the code runs completely the temp table is gone. That is why they call it temp. – paparazzo Jan 29 '14 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. – ZenoArrow Jan 28 '22 at 17:47

3 Answers3

31

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

InTheSkies
  • 999
  • 2
  • 7
  • 13
TTeeple
  • 2,913
  • 1
  • 13
  • 22
  • 3
    SQL server > Databases > System Databases > tempdb -> Temporary Tables – InTheSkies Jan 29 '14 at 19:13
  • 1
    I cannot see any temp table under the Temporary tables. – InTheSkies Jan 29 '14 at 19:16
  • 2
    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. – TTeeple Jan 29 '14 at 19:19
  • 1
    I tried it by NOT closing my connection and that did not work. I will try the breakpoint now. – InTheSkies Jan 29 '14 at 19:23
  • 1
    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 ? – InTheSkies Jan 29 '14 at 19:27
  • 1
    @InTheSkies To do that you need to not close the session to the server (via `connection.Close()` or disposing it), the server deletes all temporary tables a session creates when the session is closed. – Scott Chamberlain Jan 29 '14 at 19:32
  • 1
    @ScottChamberlain - Actually, I did not close the connection with conn.close(). That did not work. – InTheSkies Jan 29 '14 at 20:58
  • 1
    @InTheSkies Without you showing your code we can't say, perhaps it is getting closed or disposed and you don't realize it. Or perhaps the connection gets garbage collected before you go check. Please edit your original question to show the code you want to test so we can make more informed answers. – Scott Chamberlain Jan 29 '14 at 20:59
7

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

Community
  • 1
  • 1
CJBS
  • 15,147
  • 6
  • 86
  • 135
5

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')
M.Ali
  • 67,945
  • 13
  • 101
  • 127