CODEWITHSUNDEEP
sqlsap-ase
Iyas
Iyas

Reputation: 520

How do I get column type from table?

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.

Upvotes: 8

Views: 19700

Answers (5)

aF.
aF.

Reputation: 66697

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

Upvotes: 11

Michel de Ruiter
Michel de Ruiter

Reputation: 7954

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'

Upvotes: 2

Lakshman
Lakshman

Reputation: 1

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

Upvotes: 0

massoud
massoud

Reputation: 1

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

Upvotes: 0

Iyas
Iyas

Reputation: 520

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

Upvotes: 3

Related Questions