get the columns of a Temp table in sybase

sql sybase temp-tables
Charbel picture Charbel · May 9, 2011 · Viewed 7.3k times · Source

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

IAmTimCorey picture IAmTimCorey · May 14, 2011

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.

Related questions

Convert INT to VARCHAR SQL

I am using Sybase and I am doing a select which returns me a column called "iftype", but its type is int and I need to convert into varchar. When I try to do the select without the convert function …

Read More
Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF

I have the below error when I execute the following script. What is the error about, and how it can be resolved? Insert table(OperationID,OpDescription,FilterID) values (20,'Hierachy Update',1) Error: Server: Msg 544, Level 16, State 1, Line 1 Cannot insert explicit value …

Read More
From a Sybase Database, how I can get table description ( field names and types)?

I have access to command line isql and I like to get Meta-Data of all the tables of a given database, possibly in a formatted file. How I can achieve that? Thanks.

Read More