How do I get column type from table?

sql sap-ase
Iyas picture Iyas · Dec 18, 2011 · Viewed 18.5k times · Source

I have this code:

select  a.id as tableid,
    a.name as tableName, 
    b.name as columnName,
    b.status as columnStatus,
    b.type as columnType
    from sysobjects a 
    LEFT JOIN syscolumns b
    ON a.id = b.id
    WHERE a.name = 'table_name'

Now, the columType shows numbers. I want to get the name of the columnType, which resides in column 'name' in table 'systypes'. How do I do that? Simple LEFT JOIN will result in duplicate rows.

Answer

aF. picture aF. · Dec 18, 2011

I've seen in Sybase 15.0 and this is the code that you have to use:

select o.id [tableid], o.name [tableName], c.name [columnName], c.status [columnStatus], t.name [columnType] from sysobjects o
inner join syscolumns c on c.id = o.id
inner join systypes t on t.usertype = c.usertype
where o.type = 'U' and o.name in ('tablename')

Related questions

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
How to pass a comma separated list to a stored procedure?

So I have a Sybase stored proc that takes 1 parameter that's a comma separated list of strings and runs a query with in in an IN() clause: CREATE PROCEDURE getSomething @keyList varchar(4096) AS SELECT * FROM mytbl WHERE name IN (@keyList) …

Read More
Difference between left join and *= in Sybase ASE 15

I need help to understand this matter, what's the difference between the 2 queries below, all I know that they don't return the same result. Query 1: SELECT a.col1, b.c1 FROM A a LEFT JOIN B b ON a.col1 = …

Read More