How to select temporary tables in Temporary Tables

sql-server-2008 tempdb
user3011414 picture user3011414 · Mar 31, 2016 · Viewed 7.9k times · Source

Currently, I have some temp tables as this format "#A0089D2C", "#A0232241"

How could I select them to see what are its data?

I tried these queries as below:

Select * from #A0089D2C
Select * from tempdb.dbo.#A0089D2C

But I got the error:

Database name 'tempdb' ignored, referencing object in tempdb.
Database name 'tempdb' ignored, referencing object in tempdb.
Msg 208, Level 16, State 0, Line 1
Invalid object name '#A0089D2C'.

Please advise.

Thanks.

Answer

Anuj Tripathi picture Anuj Tripathi · May 18, 2016

Technically, only that user who created the local temporary table can access it within the scope. That means, local temporary table (# tables) can be accessed within the same scope by the same user while global temporary table(## tables) can be accessed among all the users until the last user session that references the table disconnects.

you can check table schema via below trick:

  • Right click on Tempdb database
  • Task > Export Data

enter image description here

  • Keep source database as TempDb
  • Select destination (Remember: you can not transfer data)
  • On Select source table and view form you will see "Edit mapping". Click on that to see table structure

enter image description here

but if you are really enthusiastic to see data in temporary table then check out this awesome post about viewing another session's Temporary table by Paul White

Related questions

How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More
How to check if a column exists in a SQL Server table?

I need to add a specific column if it does not exist. I have something like the following, but it always returns false: IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTableName' AND COLUMN_NAME = 'myColumnName') How can …

Read More