get the columns of a Temp table in sybase

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 inn开发者_开发技巧er 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?

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.

Ok, I know, very old topic - but I could not find a sufficient answer anywhere else so I researched myself with IAmTimCorey's answer as the starting point. This gave the following result:

SELECT sc.colid,
       Substring(sc.NAME, 1, 40) 'column name',
       Substring(st.NAME, 1, 40) 'type',
       sc.length,
       sc.prec,
       sc.status,
       ( CASE
           WHEN ( sc.status & 8 ) != 0 THEN 'Y'
           ELSE 'N'
         END )                   AS 'nullable',
       ( CASE
           WHEN ( sc.status & 128 ) != 0 THEN 'Y'
           ELSE 'N'
         END )                   AS 'identity'
FROM   tempdb..syscolumns sc
       INNER JOIN tempdb..sysobjects so
               ON sc.id = so.id
       INNER JOIN systypes st
               ON st.type = sc.type
                  AND st.usertype = sc.usertype
WHERE  so.NAME = 'test'
ORDER  BY sc.colid

Example:

1> create table tempdb..test(id numeric (15,0) identity, string varchar(40), num  numeric(15,0) not null, dt datetime, flt float)
2> go
1> select sc.colid, substring(sc.name, 1, 40) 'column name', substring(st.name, 1, 40) 'type', sc.length, sc.prec, sc.status, (case when (sc.status & 8) != 0 then 'Y' else 'N' end) as nullable, (case when (sc.status & 128) != 0 then 'Y' else 'N' end) as ident from tempdb..syscolumns sc inner join tempdb..sysobjects so on sc.id = so.id inner join systypes st on st.type = sc.type and st.usertype = sc.usertype where so.name = 'test' order by sc.colid
2> go
 colid  column name                              type                                     length      prec status nullable ident
 ------ ---------------------------------------- ---------------------------------------- ----------- ---- ------ -------- -----
      1 id                                       numeric                                            8   15    128 N        Y
      2 string                                   varchar                                           40 NULL      0 N        N
      3 num                                      numeric                                            8   15      0 N        N
      4 dt                                       datetime                                           8 NULL      0 N        N
      5 flt                                      float                                              8 NULL      0 N        N

(5 rows affected)
1>

Remarks:

• The detection of nullable columns has been derived from Sybase documentation, but for reasons unknown to me bit 3 of the status in tempdb..syscolumns does not change accordingly, see column num in my example. That is why I added column status anyway. For identity (bit 7) things work as expected. Any explanation would be very much appreciated.
• When using isql, start with enough width (eg -w160)
• The default column width for syscolumns.name and systypes.name is quite large, therefore I am using substring(....). Adjust the number of copied characters (last parameter of substring()) if your column names don't fit.
• By omitting 'tempdb..' from the table names this query also works for normal non-tempdb tables in case a query is preferred over using sp_xxx commands.

Try this

select sc.id, sc.number, sc.name from tempdb..syscolumns sc inner join tempdb..sysobjects so on sc.id = so.id where so.name like '%mytable%'

Without the # or any other temp..#mytable reference in your select.