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?

Thanks.
lastbattleAsked:
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.

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

Open in new window

0

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.

Thanks.
0
lastbattleAuthor Commented:
Thanks you so much
0
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.