Paul Stevens Paul Stevens - 1 year ago 83
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.