Sybase: check whether a temporary table exists

You can check for the existence of non-temporary tables (even in in tempdb) like this:

IF EXISTS (SELECT 1 FROM <databasemase>..sysobjects where type='U' and name='<tablename>' )

Unfortunately it doesn't work for temporary tables since temporary tables (e.g. #mytemptable) are created for the current connection and another connection might create another temporary table with the same name. So Sybase will create an entry in tempdb..sysobjects with a name containing some other info (like the spid):

create table #1234567890 (i1 int)

will be mapped to:
ASE pre-15: #1234567890__00000306012214068
ASE 15: #123456789000003060012214068

So you might do something like this:

IF EXISTS (SELECT 1 FROM tempdb..sysobjects where type='U' and name LIKE '<tablename>%'+convert(varchar(30),@@spid)+'%' )

Unfortunately, this will not work since you might have a connection with spid 30 and another with spid 306 and these '%' will mess everything up.

Also note that ASE 12.5 only considers the 13 first bytes of the table name. ASE 15 supports 238 bytes.

Please also note that the way these names are generated is not documented and might thus be changed without notice.

So finding out whether another session has created a temporary table with a given name is possible, but not easy and might be broken with any future version or patch.

But finding out whether such a table has been created in the same session is much easier:
 

IF object_id('<tablename>') IS NOT NULL

Note that this also works with global temporary tables (##xxxxx).