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?
LVL 1
NigelRocksAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic 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
begin
    select 'no temp test table - creating '
    create table #test1 (id int identity, column1 varchar(20))
end

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
begin
    select 'found temp test table - dropping'
    drop table #test1                                       -- or simply finish this session
end

select * from tempdb.information_schema.columns where table_name like '#test1%'   -- not there anymore
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor 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.
0
 
NigelRocksAuthor Commented:
Yeah, it's not in there.
0
 
SharathConnect With a Mentor Data 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.
0
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.

All Courses

From novice to tech pro — start learning today.