Finding #temp table in sysobjects / INFORMATION_SCHEMA

Question

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 1

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

In modern versions (SQL Server 2016+), this is even easier:

DROP TABLE IF EXISTS #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.

Answer 2

I'd prefer to query tempdb in such manner:

IF  EXISTS (SELECT * FROM tempdb.sys.objects 
             WHERE object_id = OBJECT_ID(N'tempdb.[dbo].[#MyProcedure]')
             AND type in (N'P', N'PC'))
BEGIN 
    print 'dropping [dbo].[#MyProcedure]'
    DROP PROCEDURE [dbo].[#MyProcedure]
END
GO

Answer 3

Below is how I got the columns for a temporary table:

CREATE TABLE #T (PK INT IDENTITY(1,1), APP_KEY INT PRIMARY KEY)

SELECT * FROM tempdb.INFORMATION_SCHEMA.COLUMNS c WHERE c.TABLE_NAME LIKE '#T%'