SQL Server: How to check for the existence of a temp table?
Temporary tables in SQL are created and maintained in the tempdb database. Here is the TSQL to create a temporary table called “#temp_employee” and the following that is code that lists all the tables in the tempdb database.
create table #temp_employee(emp_id int, emp_name varchar(50)) select * from tempdb.sys.tables
In the list of tables below the fifth row identifies the temp table that we have created above. The actual name of the temp table has got appended. In order to search for the existence of this table we have to do a wild card search on the tempdb like in the SQL below:
if exists(select * from tempdb.sys.tables where name like '#temp_employee%') print 'temp tables exists'