sql-server - existence - sql server drop temp table in stored procedure
What's the best way to determine if a temporary table exists in SQL Server? (4)
Check for the existence by retrieving it's object_id:
if object_id('tempdb..##clients_keyword') is not null drop table ##clients_keyword
When writing a T-SQL script that I plan on re-running, often times I use temporary tables to store temporary data. Since the temp table is created on the fly, I'd like to be able to drop that table only if it exists (before I create it).
I'll post the method that I use, but I'd like to see if there is a better way.
The OBJECT_ID function returns the internal object id for the given object name and type. 'tempdb..#t1' refers to the table #t1 in the tempdb database. 'U' is for user-defined table.
IF OBJECT_ID('tempdb..#t1', 'U') IS NOT NULL DROP TABLE #t1 CREATE TABLE #t1 ( id INT IDENTITY(1,1), msg VARCHAR(255) )
IF Object_Id('TempDB..#TempTable') IS NOT NULL BEGIN DROP TABLE #TempTable END
SELECT name FROM sysobjects WHERE type = 'U' AND name = 'TempTable'