How to get a list of user defined data types on Sybase ASE 15.x?

[rg...@vbrad.com]

I use the following query to get the list of user defined data types (UDDTs):

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

On Sybase 12.5, it used to return to return a single row per UDDT. Now with Sybase 15.x, it returns 2 rows:

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

The datatype of the UDDT is actually varchar. I am not sure where the longsysname is coming from. What is the correct way to return the list of UDDTs that works in both 12.5.x and 15.x ASE databases?

[Abhishek Goyal]

issue resolved or not ??

it is because when you use 32 bit on 12.5 its shows single row
but when you have 64 bit on 15.X its shows 2 rows

hope it will because of this

[adarsh.si...@gmail.com]

select
convert(char(15),a.name) [user type]
,(select convert(varchar(10),b.name) from systypes b where b.type=a.type having b.usertype = min(b.usertype))
+ case
when (select b.name from systypes b where b.type=a.type having b.usertype = min(b.usertype) )='char'
then '('+convert(varchar(10),a.length)+')'
end
,a.prec
,a.scale
FROM systypes a
WHERE accessrule != NULL
go