How to get a list of custom data types in Sybase ASE?

Question

I use the following query to get a list of user data types (UDDT):

SELECT USER_NAME(S.uid) as owner, S.name as Name, T.name as datatypename, OBJECT_NAME(S.domain), OBJECT_NAME(S.tdefault) FROM systypes S,systypes T WHERE S.type=T.type AND T.usertype<100 AND T.usertype NOT IN(18,24,25,80) AND S.usertype>100 ORDER BY 1, 2 

In Sybase 12.5, it came back to return one row on UDDT. Now with Sybase 15.x it returns 2 rows:

 owner Name datatypename 'dbo' 'auid' 'varchar' 'dbo' 'auid' 'longsysname' 

The UDDT data type is actually varchar . I'm not sure where the longsysname comes longsysname . What is the correct way to return a UDDT list that works on both ASE 12.5.x and 15.x databases?

Answer 1

Try this code:

 select s1.name, (select name from systypes s2 where s2.usertype=( select min( s3.usertype) from systypes s3 where s3.hierarchy=s1.hierarchy) ) base_type, USER_NAME(s1.uid) as owner from systypes s1 WHERE s1.usertype>100