Tuesday, March 27, 2012

Getting the Base Type from User Defined Data Types

Suppose I have a user defined data type in SQL Server 2000 called
USER_NAME which was created using:
sp_addtype USER_NAME, 'Char (10)'
Is there a query that I can use to display what the base type is? For
instance, I'd like to query a table like systypes and have it tell me
that USER_TYPE is Char(10).
I can see that systypes has an xtype field, but it contains a number.
Is there somewhere that maps that number to a physical datatype?You could also do this as a join...
SELECT s1.name, type = (SELECT name
FROM systypes s2
WHERE s2.xtype = s1.xtype
AND s2.xusertype<=256
),
s1.length
FROM systypes s1
where s1.xusertype > 256
"Janel E." <jmeichler@.oasvas.com> wrote in message
news:b5dd350d.0308060839.7dd690da@.posting.google.com...
> Suppose I have a user defined data type in SQL Server 2000 called
> USER_NAME which was created using:
> sp_addtype USER_NAME, 'Char (10)'
> Is there a query that I can use to display what the base type is? For
> instance, I'd like to query a table like systypes and have it tell me
> that USER_TYPE is Char(10).
> I can see that systypes has an xtype field, but it contains a number.
> Is there somewhere that maps that number to a physical datatype?

No comments:

Post a Comment