[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 891
  • Last Modified:

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.
0
lastbattle
Asked:
lastbattle
  • 2
1 Solution
 
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
 
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now