How should I find the temp tables are exists in database?
For a normal SQL table we will do the check in sysobjects similar to below,
IF EXISTS (SELECT 1
FROM sysobjects
WHERE xtype='u' AND name='tablename')
Select 'Exists'
But, this is not the right way of checking the existence of a temporary table. .
So, the correct way of finding its existence is by,
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
PRINT 'Exists'
ELSE
PRINT 'Not Exists'
OBJECT_ID function will return the object id of the table and if it is not null then we can confirm its existence.
Some info about temp table:
- When we create a new temp table it is stored in "TempDB" database by default.
- Since it will be created for every session there will be an identifier that gets appended to every temp table name
|