How do I get column type from table?

Question

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.

score 11 · Accepted Answer · edited May 24 '17 at 18:55

11

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')

edited May 24 '17 at 18:55

user1477388

20,790
32
144
264

answered Dec 18 '11 at 14:23

aF.

64,980
43
135
198

It seems that in table systypes, there are duplicate entries for column 'type' with value 39. This explains why i get extra rows using your code. The column 'name' values are 'sysname', 'nvarchar', 'varchar' for type = 39. – Iyas Dec 19 '11 at 01:26
My 2nd answer got lost in the space! I found similar answer from mssql syntax, the only different is mssql use xusertype. So I accept your answer. Thanks aF.! – Iyas Dec 21 '11 at 01:47
2

using `usertype` instead of `type` in the where clause seems to be giving required result. Although this is an old thread, just confirming in case someone else stumbles across with similar problem. – Gentle Jul 18 '12 at 12:14
the query is missing a comma after c.status [columnStatus] – Tom Hanley Aug 24 '16 at 13:12

score 3 · Answer 2 · answered Dec 19 '11 at 02:15

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.type = c.type
where o.type = 'U' and o.name = 'table_name'
and t.name not in ('sysname', 'nid', 'uid', 'nvarchar', 'tid', 'nchar')

I add the last line from @aF. code. I don't know how to explain this. I got this answer from here: http://www.dbforums.com/sybase/913004-getting-column-type.html#post3355703

That is wrong. I've seen it and you have to define the systypes type as User (U) and link it with usertype :) — aF., Dec 19 '11 at 09:14

score 2 · Answer 3 · answered Jun 24 '16 at 09:15

It is necessary to join on both type and usertype:

SELECT
 o.id     AS tableID,
 o.name   AS tableName,
 c.name   AS columnName,
 c.status AS columnStatus,
 t.name   AS typeName
FROM       syscolumns AS c NOLOCK
INNER JOIN systypes   AS t NOLOCK
 ON  c.type     = t.type
 AND c.usertype = t.usertype
INNER JOIN sysobjects AS o NOLOCK
 ON c.id = o.id
INNER JOIN sysusers   AS u NOLOCK
 ON o.uid = u.uid
WHERE o.name = 'table_name'
 AND  u.name = 'dbo'

The accepted answer does not give me the correct result, but this does. — Dan, May 15 '17 at 16:03

score 0 · Answer 4 · answered Dec 12 '13 at 10:30

create function dbo.GetColumnDataTypeName(@TableName TName, @ColumnName TName)
returns sysname
as
begin
    declare @typeName sysname,
        @tableId int = object_id(@TableName)
    select
    @typeName = type_name(user_type_id)
    from sys.columns sc
    where sc.[object_id] = @tableId
    and sc.Name = @ColumnName

    return @typeName
end
Go

score 0 · Answer 5 · edited Feb 24 '16 at 19:38

select so.name as table_name, '' as table_description,sc.name as fileld_name,'' as field_description,
    st.name as format,
    sc.length as data_length,sc.prec as decimal_places,'' as primary_key,
     case when sc.status = 8 then 'Y'
            else 'N'
     end as nulls
    from syscolumns sc
    INNER JOIN sysobjects so ON sc.id = so.id
    inner join systypes st on sc.type = st.type
    WHERE so.name in (/*table list*/)
    and st.name not in ('sysname', 'nid', 'uid', 'nvarchar', 'tid', 'nchar','longsysname','usr_char')
    order by so.name   
    go

How do I get column type from table?

5 Answers5