CODEDIGEST
Home » FAQs
Search
 

Technologies
 

How should I find the temp tables are exists in database?
Submitted By Satheesh Babu B
On 1/31/2009 1:14:42 AM
Tags: Interview Questions,sql  

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

 

Recent FAQs
  • View All FAQs..