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