A way to check if the temporary table exists or not

Question

I have this following query :

IF NOT EXISTS (SELECT 1
               FROM   sysobjects
               WHERE  id = Object_id('tempdb..TEMP_THETH_DETAILS'))
  EXECUTE (
'CREATE TABLE tempdb..TEMP_THETH_DETAILS( THETH_ID NUMERIC(5) NOT NULL, LANGUAGE VARCHAR(3) DEFAULT ''EN'' NOT NULL)'
)

GO

The problem is the checking, it seems tempdb doesnt take on consideration if not exist, maybe because the table was create in the tempdb.
So my question is there a way I can check if the temporary table exists or not ?

Robert · Accepted Answer · 2013-06-21T09:02:29.513

9

Try this:

IF object_id('tempdb..TEMP_THETH_DETAILS') is null
begin
   EXECUTE 
   (
       'CREATE TABLE tempdb..TEMP_THETH_DETAILS
        ( THETH_ID NUMERIC(5) NOT NULL, 
          LANGUAGE VARCHAR(3) DEFAULT ''EN'' NOT NULL
        )'
    )
end
go

edited Jun 21 '13 at 09:02

answered Jun 21 '13 at 08:50

Robert

25,425
8
67
81

parado I dont want to create it as #temp_ .. because i want the table to be insrted in the MDB sybase database , creating as #tem will be deleted when the sesions ends which I dont want that. its like similar in oracle global temporary table – Moudiz Jun 21 '13 at 09:00
I'm glad I cloud help you! – Robert Jun 21 '13 at 11:15

score 0 · Answer 2 · edited Sep 14 '17 at 15:18

0

You can catch exception when checking for table existence and execute code if an exception was intercepted.

In Sybase:

begin

  declare SQLE_TABLE_NOT_FOUND EXCEPTION FOR SQLSTATE '42W33';

  ...

  if exists(select 'x
              from #tmpTable) then

    -- TODO

  endif;

  -- at the end of the function if table not exists,

  -- thrown exception is catched here

  exception when SQLE_TABLE_NOT_FOUND then

    -- TODO

end;

edited Sep 14 '17 at 15:18

C. Helling

1,394
6
20
34

answered Sep 14 '17 at 14:23

Jenya Miachyn

104
1
5

uhmm its an old question, however i will check you answer maybe something new for me – Moudiz Sep 14 '17 at 14:31

score -2 · Answer 3 · edited Jan 24 '17 at 07:36

-2

use tempdb

GO

IF NOT EXISTS (SELECT 1
           FROM   sysobjects
           WHERE  id = Object_id('tempdb..TEMP_THETH_DETAILS'))
EXECUTE (
    'CREATE TABLE tempdb..TEMP_THETH_DETAILS( THETH_ID NUMERIC(5) NOT NULL,
    LANGUAGE VARCHAR(3) DEFAULT ''EN'' NOT NULL)'
)

GO

edited Jan 24 '17 at 07:36

CroMagnon

1,218
7
20
32

answered Jan 24 '17 at 06:42

Mikee

1

1

sorry what is the difference ? – Moudiz Jan 24 '17 at 07:50

A way to check if the temporary table exists or not

3 Answers3