• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 719
  • Last Modified:

Where Are the Temp Tables?

I've created a temp table in SQL Server, but I can't see it anywhere in Management Studio?  Where is it?
3 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
it is in tempdb database.
but you might not see it there, presuming that the SSMS uses a different session that where you created the temp tables with.
NigelRocksAuthor Commented:
Yeah, it's not in there.
SharathData EngineerCommented:
Create a temp table as
CREATE TABLE #TempTable(Col1 int)
After creating, expand the Databases -> System Databases -> tempdb -> Temporary Tables node. You will see the temp table you have created.
Once you close the session in which you have created the temp table, you can not see you temp table in the Temporary tables list in tempdb database.
Mark WillsTopic AdvisorCommented:
as the guys above have said, temp tables are in tempdb... but only survive for the current session - they are automatically dropped.

try this little test (and maybe some handy code as well) to convince yourself. Going into SSMS and open a query window against your favourite database...

if object_id('tempdb..#test1','U') is NULL
    select 'no temp test table - creating '
    create table #test1 (id int identity, column1 varchar(20))

insert #test1 (column1) values ('column1')     -- will insert just fine

select * from #test1

select * from tempdb.information_schema.columns where table_name like '#test1%'   -- note the 'real' table name

if object_id('tempdb..#test1','U') is NOT NULL
    select 'found temp test table - dropping'
    drop table #test1                                       -- or simply finish this session

select * from tempdb.information_schema.columns where table_name like '#test1%'   -- not there anymore
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now