Results 1 to 4 of 4

Thread: systypes

  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: systypes

    I'm trying to construct a SQL statement (v 12.5) which displays table fields and datatypes, but I'm having a problem trying to figure out how Sybase stores it's types in the systypes table. I'm getting multiple datatypes for single fields. What am I missing in this table?

    Code:
    select obj.name as table_name, 
              col.name as column_name,
              typ.name,
              CASE WHEN col.prec is not null and col.scale is not null then 
                                "(" + convert(varchar(10), col.prec) + ", " + convert(varchar(38), col.scale) + ")"
                         WHEN typ.name in ("char", "varchar") then
                                "(" + convert(varchar(10), col.length) + ")"
                         ELSE 
                                null         
              END
    from sysobjects obj, 
            syscolumns col,
            systypes typ
    where obj.id = col.id and 
              col.type = typ.type and
              obj.type = "U" and 
              obj.name = "ps_scheduling_letter"
    
    table_name                     column_name                    name                                                                                
    ----------                     -----------                    ----                           -                                                    
    ps_scheduling_letter           year                           int                            NULL                                                 
    ps_scheduling_letter           empnr                          int                            NULL                                                 
    ps_scheduling_letter           selected_for_hh                char                           (1)                                                  
    ps_scheduling_letter           selected_for_hh                nchar                          NULL                                                 
    ps_scheduling_letter           selected_for_cl                char                           (1)                                                  
    ps_scheduling_letter           selected_for_cl                nchar                          NULL                                                 
    ps_scheduling_letter           name                           nvarchar                       NULL                                                 
    ps_scheduling_letter           name                           sysname                        NULL                                                 
    ps_scheduling_letter           name                           varchar                        (50)
    -Chuck

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    You should probably take a look at the sp_help source code to see how Sybase does this:
    Code:
           select Column_name = isnull(c.name, 'NULL'),
                   Col_order = colid,
                   Type = isnull(convert(char(30), x.xtname),
                                    isnull(convert(char(30),
                                            get_xtypename(c.xtype, c.xdbid)),
                                    t.name)),
                   Length = c.length,
                   Prec = c.prec,
                   Scale = c.scale,
                   Nulls = convert(bit, (c.status & 8)),
                   Default_name = object_name(c.cdefault),
                   Rule_name = object_name(c.domain),
                   Access_Rule_name = object_name(c.accessrule),
                   rtype = t.type, utype = t.usertype, xtype = c.xtype,
                   Ident = convert(bit, (c.status & 0x80)),
                   Object_storage =
                            case when (c.xstatus is null) then NULL
                                 when (c.xstatus & 1) = 1 then "off row"
                                 else                          "in row " end
            into #helptype
            from syscolumns c, systypes t, sysxtypes x
                    where c.id = object_id(@objname)
                            and c.usertype *= t.usertype
                            and c.xtype *= x.xtid
    This code checks for extended datatypes (Java objects) as well, so you can probably remove the sysxtypes table from your select.

    Michael

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    That get's me what I need, thanks!
    Where did you locate the source code for sp_help, btw?
    -Chuck

  4. #4
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    The source code for the system stored procs is in the sybsystemprocs database. Just run
    Code:
    use sybsystemprocs
    go
    sp_helptext <name of the proc>
    go
    to get at it...

    Michael

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •