Temporary Table Existence and Scope

To find the existence of a temp table you can use the OBJECT_ID() function.

If the OJBECT_ID function returns a value then the table exists.  This works for both local and global temporary tables.  This example uses the AdventureWorks test database but any database will do.

--setup
--test to see if the temp tables already exist before trying to create them.
--creating a table that already exists raises an error.
IF NOT (object_id('TempDb..#Test1','U') IS NOT NULL)
CREATE TABLE #Test1 (ID int, Data varchar(10))

IF NOT (object_id('TempDb..##Test2','U') IS NOT NULL)
CREATE TABLE ##Test2 (ID int, Data varchar(10))

--test
--if they exist return the object_id of the table.
IF (object_id('TempDb..#Test1','U') IS NOT NULL)
SELECT 'Local Temp Table Object ID',(OBJECT_ID('TempDB..#Test1'))

IF (object_id('TempDb..##Test2','U') IS NOT NULL)
SELECT 'Global Temp Table Object ID',(OBJECT_ID('TempDB..##Test2'))

--cleanup
--make sure the tables exist before we drop them.
--dropping a non existent table raises an error.
IF (object_id('TempDb..#Test1','U') IS NOT NULL)
DROP TABLE #Test1

IF (object_id('TempDb..##Test2','U') IS NOT NULL)
DROP TABLE ##Test2

To test the scope, run the create and test portions of the script in one query window and the test portion in a second query window. The global temp table is found but the temp table that was local to the other connection is not.

Though not required the object_type parameter of the OBJECT_ID function will help you determine if the temporary object is type of object you think it is.

This also creates a temporary object called #TestProc:

CREATE PROCEDURE #TestProc
AS
SELECT 1

Test this temporary stored procedure with:

EXEC #TestProc

Using the OBJECT_ID function determines that this object does exist:

IF (object_id('TempDb..#TestProc') IS NOT NULL)
SELECT object_id('TempDb..#TestProc')

However if you specify that you are looking for objects that are temporary tables with that name specifying the object type will make it appear that an object named #TestProc does not exist:

IF (object_id('TempDb..#TestProc','U') IS NOT NULL)
SELECT object_id('TempDb..#TestProc')

Whether you use the object_type parameter really depends on what you are trying to do. Since two temp objects in the same scope can’t exist with the same name (even if they are different object types) finding out if an object with the same name exists, regardless of what type of object it is, might be important to you.