How to get an object ID from temporary table using object_id function

Question

I have created a table as below:

create table #tab
(
  id int
)

Now, I want to get an object id of the table.

I tried on the same session:

select object_id( "#tab" ) 

and

select object_id( "tempdb..#tab" )

but both return null.

Answer 1

Short answer...on IQ this isn't possible. Long answer...you have a few fairly good choices and some not so good choices.

1. Rewrite the entire procedure in old watcom SQL, trap the error if the operation to drop the table fails...

2. Use a permanent table (no effective difference between the 2 in IQ as far as I know)

3. Get funky...and use odd IQ behavior! If you create a temp table outside a transaction, then check @@trancount...you will get 0 as you expect. If you then open a transaction...and check @@trancount you will get 2. So...consider that a successful temp table creation :)

4. Just assume it doesn't exist on your connection :)

Sybase ASA SQL Code list: http://manuals.sybase.com/onlinebooks/group-sas/awg0800e/dberen8/@Generic__BookTextView/334;pt=334#X

Example for #1:

DROP PROCEDURE foo;
go
create procedure foo()
begin
  DECLARE DROP_TABLE_FAILED EXCEPTION FOR SQLSTATE '42W33';

  BEGIN
    DROP TABLE T1;
    EXCEPTION 
      WHEN DROP_TABLE_FAILED 
      THEN 
      WHEN OTHERS THEN RESIGNAL;

  END;

    CREATE LOCAL TEMPORARY TABLE t1 (c1 int) 
    on commit preserve rows;

    insert into t1 select 1;

    select * from t1;

END;
go
exec foo
go
exec foo
go
drop table t1;
go