Where temp tables are located?

Question

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.

Answer 1

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....

Answer 2

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.