Mapping User Defined Data Type to Base Data Type

Hi,

I am trying to map a user-defined datatype to it's base data type in SQL Server 2000/2005. Let's say I have created a udt named ssn which is actually a char datatype with length 9. I need a query that would map the udt with the base datatype and give the typename of both. I have been using the sys.types table but I still can't see the link. Any help would be appreciated.

Thanks

Here is an Information_Schema view that I created a long time ago in my databases. Most of it I actually copied from a sql 2000 system sproc. You should be able to use it to construct what you need:

SELECT TOP 100 PERCENT
*,
ColumnName + ' ' +
UsedDataType +
CASE WHEN UserDefinedDataType IS NULL THEN
CASE WHEN collationID IS NOT NULL THEN
'('+CAST(CharacterMaxLength AS VARCHAR(10))+')'
ELSE ''
END
ELSE
''
END
AS ColumnDefinition,
ColumnName + ' ' +
UPPER(basedatatype) +
CASE WHEN collationID IS NOT NULL THEN '('+CAST(CharacterMaxLength AS VARCHAR(10))+')' ELSE '' END
AS ColumnDefinition2,
UsedDataType +
CASE WHEN UserDefinedDataType IS NULL THEN
CASE WHEN collationID IS NOT NULL THEN
'('+CAST(CharacterMaxLength AS VARCHAR(10))+')'
ELSE ''
END
ELSE
''
END
AS DefinedDataType
FROM
(
SELECT
DB_NAME() AS DatabaseName,
CASE obj.xtype WHEN 'U' THEN 'TABLE' WHEN 'V' THEN 'VIEW' WHEN 'P' THEN 'PROCEDURE' END AS ObjectType,
USER_NAME(obj.uid) AS TableSchema,
obj.name AS TableName,
col.name AS ColumnName,
col.colid AS ColumnPosition,
com.text AS DefaultValue,
CASE col.isnullable WHEN 1 THEN 'YES' ELSE 'NO' end AS IsNullable,
spt_dtp.LOCAL_TYPE_NAME AS BaseDataType,
CASE WHEN typ.xusertype > 256 THEN typ.name ELSE UPPER(typ.name) END AS UsedDataType,
CONVERT(INT, OdbcPrec(col.xtype, col.length, col.xprec) + spt_dtp.charbin) AS CharacterMaxLength,
NULLIF(col.xprec, 0) AS NumericPrecision,
col.scale AS NumericScale,
CONVERT(SYSNAME, CASE WHEN typ.xusertype > 256 THEN typ.name ELSE NULL END) AS UserDefinedDataType,
OBJECT_NAME(cdefault) AS ColumnDefaultName ,
typ.CollationID
FROM
sysobjects obj,
master.dbo.spt_datatype_info spt_dtp,
systypes typ,
syscolumns col
LEFT OUTER JOIN syscomments com on col.cdefault = com.id AND com.colid = 1,
master.dbo.syscharsets a_cha
WHERE
obj.id = col.id AND
typ.xtype = spt_dtp.ss_dtype AND
(spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2) AND
obj.xtype in ('U', 'V', 'P') AND
col.xusertype = typ.xusertype AND
(
spt_dtp.AUTO_INCREMENT IS NULL OR spt_dtp.AUTO_INCREMENT = 0) AND
a_cha.id = ISNULL(CONVERT(TINYINT, CollationPropertyFromID(col.collationid, 'sqlcharset')),
CONVERT(TINYINT, ServerProperty('sqlcharset'))
) and obj.type = 'u'

) a
ORDER BY
TableName, ColumnPosition ASC