CODEWITHSUNDEEP
sqlsql-serversql-server-2005sql-server-2008
atricapilla
atricapilla

Reputation: 2640

Where temp tables are located?

If I create a temporary table using # sign:

SELECT * INTO #temp FROM dbo.table

Where is this table located? I can't find this from tempdb.

Upvotes: 8

Views: 30387

Answers (5)

Cdl
Cdl

Reputation: 129

I suspect this issue rose from the fact that if you don't right click and refresh the 'Temporary Tables' folder, SSMS will not show you the temp table immediately.

Upvotes: 0

gngolakia
gngolakia

Reputation: 2216

Local temp tables can be created using hash (#) sign prior to table name.

They are visible only in current connection. When connection is dropped its scope ends as well.

It is possible to create and use local temp table with the same name simultaneously in two different connections.

Read More

http://sqlnetcode.blogspot.com/2011/11/there-is-already-object-named-temp-in.html

Upvotes: 0

Karel
Karel

Reputation: 2212

How are you looking for them? If you do a select you'll get the data. But the table is only available in the session, just for the user who created it (you can have global temp tables).

They are stored in temp db.

Upvotes: 0

Sachin Shanbhag
Sachin Shanbhag

Reputation: 55529

When you declare a temporary table, SQL Sever adds some additional characters on its name in order to provide a unique system name for it and then it stores it in tempDB in the sysobjects table. Even though you can query the temporary table with its logical name, internally is known with the exact name SQL Server has set.

Upvotes: 1

marc_s
marc_s

Reputation: 755381

Those tables are created in your tempDB - but the table name might not be exactly as you defined.

In my case, I get:

#temp______________________________000000000003

Try this:

SELECT * INTO #temp FROM dbo.table
SELECT * FROM tempdb.sys.tables

You should see an entry for that temp table you've just created....

Upvotes: 8

Related Questions