Finding #temp table in sysobjects / INFORMATION_SCHEMA

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.


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%'

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