Paul Stevens Paul Stevens - 2 years ago 106
SQL Question

Finding #temp table in sysobjects / INFORMATION_SCHEMA

I am running a

statement like this so I can manipulate the data before finally dropping the table.

SELECT colA, colB, colC INTO #preop FROM tblRANDOM

However when I run the statement and then, without dropping the newly created table, I then run either of the following statements, the table isn't found? Even scanning through object explorer I can't see it. Where should I be looking?

SELECT [name] FROM sysobjects WHERE [name] = N'#preop'

Answer Source

Temp tables aren't stored in the local database, they're stored in tempdb. Also their name isn't what you named them; it has a hex code suffix and a bunch of underscores to disambiguate between sessions. And you should use sys.objects or sys.tables, not the deprecated sysobjects (note the big warning at the top), or the incomplete and stale INFORMATION_SCHEMA views.

SELECT name FROM tempdb.sys.objects WHERE name LIKE N'#preop[_]%';

If you are trying to determine if such an object exists in your session, so that you know if you should drop it first, you should do:

IF OBJECT_ID('tempdb.dbo.#preop') IS NOT NULL
  DROP TABLE #preop;

However if this code is in a stored procedure then there really isn't any need to do that... the table should be dropped automatically when the stored procedure goes out of scope.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download