How to get sybase table column name and its datatype and order by?

Question

There are multiple table in my sybase database. I want to know the column name and datatype of a given table like (myOrder table). How can I do this? Below script I found on stackoverflow From a Sybase Database, how I can get table description ( field names and types)? . But this gives me exception syscolumns is ambiguous? The script is below that I used for this.

SELECT sc.* 
FROM syscolumns sc
INNER JOIN sysobjects so ON sc.id = so.id
WHERE so.name = 'my_table_name'

Answer 1

Just to add a hopefully useful addition here, I use Sybase's SQL Anywhere 12, so, the syscolumns object(view) does not hold the same information as other versions of Sybase, and therefore requires joining on named information. However, this consolidated view, as it is called, is almost perfectly capable by itself (see it's documentation here).

We in fact use it for checking changes to columns across all tables, as a QA tool, similar to this:

Select * 
    From sys.syscolumns 
    Where (cname Like '%trk%' Or cname Like '%track%') 
    And cname not like '%ontrack%' 
    Order By tname;

Answer 2

You can use built-in procedure sp_columns. It will return all the table metadata including column name, data type, column length etc. for a given table.

sp_columns table_name

Answer 3

To get column names, data types and a lot more info for a table in Sybase, use the following query.

Select * from systabcol
key join systab
 where table_name = 'your_table_name'

Answer 4

To extract types I am using such query:

SELECT syscolumns.name, systypes.name FROM sysobjects 
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON systypes.type = syscolumns.type AND systypes.usertype = syscolumns.usertype
WHERE sysobjects.name LIKE 'my_table' 

Answer 5

Sybase Central gets the columns like this:

select
    col.name,
    isnull(xt.xtname, isnull(get_xtypename(col.xtype, col.xdbid), t.name))
from
    syscolumns col
join
    sysobjects obj
on
    col.id = obj.id
left outer join
    systypes t
on
    col.usertype = t.usertype
left outer join
    sysxtypes xt
on
    col.xtype = xt.xtid
where
    obj.name like 'sysobjects'
order by
    col.colid