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.

Answers


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)

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


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

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

Need Your Help

Is it possible to advance a deadlocked thread? stuck at WaitForSingleObject

windows multithreading delphi winapi deadlock

If I have an app that is creating threads which do their work and then exit, and one or more threads get themselves into a deadlock (possibly through no fault of my own!), is there a way of

Inner Class method access to parent method's variable

java

Why can't method r access variable c? I know it can't access the variable but I'm trying to understand why it doesn't.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.