Paul Stevens Paul Stevens - 6 months ago 18
SQL Question

Finding #temp table in sysobjects / INFORMATION_SCHEMA

I am running a

SELECT INTO
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'
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '#preop'

Answer

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
BEGIN
  DROP TABLE #preop;
END

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.

Comments