Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2008
    Posts
    1

    Question Unanswered: Columns datatype

    Hi,

    First post ! Hello to all the dbForums community !!

    I'm working on a Sybase to Oracle migration. To check our migration, we'd like to 'diff' different kinds of information between source Sybase DB and destination Oracle DB.

    My question is :
    I want to find all columns of a particular datatype (e.g. 'float').
    My result should look like :
    TABLE_NAME, COLUMN_NAME, DATATYPE
    I've tried to use both syscolumns and systypes tables but the datatype returned looks like a user defined type, not the 'base' datatype.

    Code:
    SELECT syscolumns.name COLUMN_NAME, systypes.name DATATYPE
    FROM syscolumns, systypes
    WHERE syscolumns.id = object_id('table_name')
    AND systypes.usertype=syscolumns.usertype
    GO
    How should I bring back the real 'base' datatype from the user defined datatype ?

    Thanks for your help.

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    SELECT syscolumns.name COLUMN_NAME, systypes.name DATATYPE
    FROM syscolumns, systypes
    WHERE syscolumns.id = object_id('table_name')
    AND systypes.usertype=syscolumns.usertype
    AND systypes.usertype < 100
    UNION ALL
    SELECT c.name COLUMN_NAME, t.name DATATYPE
    FROM syscolumns c, systypes u, systypes t
    WHERE c.id = object_id('table_name')
    AND u.usertype=c.usertype
    AND u.type = t.type
    AND u.usertype > 100
    AND t.usertype < 100

Posting Permissions

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