Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Question Getting column type

    I'm still fairly new in the Sybase support running ASA8. Anyway vendor delivered app & DB. Trying to extract data.

    I want to pull in the column type (i.e. varchar, integer...) from the DB. I've got this down as the query:

    SELECT SYSTABLE.TABLE_ID, SYS.SYSTABLE.TABLE_NAME, SYS.SYSCOLUMN.COLUMN_NAME,
    SYS.SYSCOLUMN.PKEY, SYS.SYSCOLUMN.NULLS, SYS.SYSCOLUMN.WIDTH
    FROM SYS.SYSTABLE, SYS.SYSCOLUMN
    WHERE SYSTABLE.TABLE_ID IN (SELECT TABLE_ID FROM SYS.SYSCOLUMN)
    AND SYSTABLE.TABLE_ID = SYSCOLUMN.TABLE_ID
    AND UPPER(LEFT(SYS.SYSTABLE.TABLE_NAME,3)) <> 'SYS'


    1. Is it the systypes table.
    2. Most of the user_type column is null for the syscolumns table.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  2. #2
    Join Date
    Jan 2003
    Posts
    26

    Re: Getting column type

    I think this might be close to what you're looking for:
    select a.id, a.name, b.colid, b.name, c.name, b.length
    from sysobjects a, syscolumns b, systypes c
    where a.name in (select name from sysobjects where type = 'U')
    and a.id = b.id
    and b.type = c.type
    and c.name not in ('sysname', 'nid', 'uid', 'nvarchar', 'tid', 'nchar')

  3. #3
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Getting column type

    Originally posted by frankp
    I think this might be close to what you're looking for:
    select a.id, a.name, b.colid, b.name, c.name, b.length
    from sysobjects a, syscolumns b, systypes c
    where a.name in (select name from sysobjects where type = 'U')
    and a.id = b.id
    and b.type = c.type
    and c.name not in ('sysname', 'nid', 'uid', 'nvarchar', 'tid', 'nchar')
    No joy. That query is broken.

    But you gave me the answer I needed anyway.

    I was trying to build my own version of SYSCOLUMNS. If you note in my query I was going against SYSCOLUMN (no "S"). That was my bad. What I was really looking for was SYSCOLUMNS. Comes from being in Oracle too long, first.

    Thanks
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  4. #4
    Join Date
    Jan 2003
    Posts
    26

    Re: Getting column type

    That query works fine on 11.9.2. I tried it on a couple of servers.

  5. #5
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Getting column type

    Originally posted by frankp
    That query works fine on 11.9.2. I tried it on a couple of servers.
    They must have made changes to the database structure for ASA 8 vs 11.9.2.

    Thanks again.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

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