CODEWITHSUNDEEP
sql-server-2008tempdb
user3011414
user3011414

Reputation: 119

How to select temporary tables in Temporary Tables

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.

Upvotes: 3

Views: 12632

Answers (2)

Anuj Tripathi
Anuj Tripathi

Reputation: 2281

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

Upvotes: 3

SnakeFoot
SnakeFoot

Reputation: 174

this should work, at least if the temp-table exists at the moment. 

Select * from tempdb.dbo.#A0089D2C

See, temp tables are like the Schrodinger cat, they exists and they don't in the same time ;)

Upvotes: -1

Related Questions