How can I get the based data type of user-defined datatype in Sybase?

Which system tables in Sybase contain information about the based datatype of user-defined datatype?
I know that we can use sp_help <user-defined datatype name> to get information about the based datatype.

What I want is I would like to retrieve the based-datatype of user-defined datatype using SELECT statement by myself? systypes table contain datatype information including user-defined datatype of all the fields in the tables. But it doesn't contaion the based-dataype of user-defined data type.

How can I do that?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I don't have a sybase db handy to try it, but I hear tell something like this will do it.
	DATA_TYPE = ISNULL(real_types.Storage_type,, 
        NUMERIC_SCALE = C.scale, 
        (C.status & 0x08))
FROM syscolumns C, systypes T, sysobjects A,
	(select User_type =, Storage_type = 
        from systypes s, systypes st 
        where s.type = st.type and s.usertype > 99 
              and not in ('sysname', 'longsysname', 'nchar', 'nvarchar') 
              and st.usertype < 100) real_types
        AND = 
        AND = OBJECT_ID(?) 
        AND C.usertype* = T.usertype
        AND* = real_types.User_type " + "ORDER BY C.colid

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lastbattleAuthor Commented:
We got some idea from your query though it is not completely fit with our situation.

Now it is solved.

lastbattleAuthor Commented:
Thanks you so much
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Sybase Database

From novice to tech pro — start learning today.