We help IT Professionals succeed at work.

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

Medium Priority
907 Views
Last Modified: 2012-05-05
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.
Comment
Watch Question

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

Author

Commented:
We got some idea from your query though it is not completely fit with our situation.

Now it is solved.

Thanks.

Author

Commented:
Thanks you so much