get the columns of a Temp table in sybase

Question

Is there a way to get the list of columns of a temporary table in sybase? suppose I have a table called #mytable

select count (*) from  tempdb..#mytable

return 145 to say there are 145 rows in this table. I tried the following (with a few variation)

select so.name from tempdb..syscolumns sc inner join tempdb..sysobjects so on sc.id = so.id where so.name = '#mytable'

also tried

select so.name from tempdb..syscolumns sc inner join tempdb..sysobjects so on sc.id = so.id where so.name = 'tempdb..#mytable'

both came back with empty result.

any ideas? any other primitives to get the column names of a temp table in sybase?

Answer 1

I apologize but I don't have Sybase to try this out on. However, I can give you what I believe to be the answer but it might take a bit of legwork on your part to get the syntax right. Basically, according to the documentation you can use the sp_help command on your temp table as long as you do it from the tempdb. Here is the quote from Sybase:

System procedures such as sp_help work on temporary tables only if you invoke them from tempdb.

Reference

Here is how you would use the sp_help command:

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.sprocs/html/sprocs/sprocs118.htm

The reason that the OBJECT_ID(tempdb..#mytable) command did not work is because that table name does not exist in that table. The reason being is that Sybase ensures that all temp tables are unique. In order to do so, it takes the temp table name (including the pound sign) and truncates it to 13 characters, appends underscores to make it 13 characters (in case it was short), and adds a 17-digit session ID to the end of the table name. This way you can have a temp table named #mytable and another user (or even you in another session) can have the exact same name for a temp table without causing conflict. If you figured out your session ID, you could probably build your temp table name. If you built the temp table name, you could assign it to a variable (say @newTableName) and use the SELECT name FROM tempdb..syscolumns WHERE id = OBJECT_ID(@newTableName) method to retrieve your temp table columns.