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

Manipulating Namespace Attributes

Hello All:
I have a fair amount of SQL 2000 experience and recentlty have begun
working with SQL 2005. SPecificall, I am working with the XML data
type in the following scenario:
-A third party vendor provides us with a data source containing a
column of datatype XML. I would like to search through the nodes of
the XML for specific elements and attributes. The problem is that some
of the XML fields contain namespaces (meaning that my xQuery must
declare the namespace as well), and some of the values in this same XML
column do not have namespaces declared. I have no control over the
data sent to me, but what I would like to do is remove the namespace
attribute where it exists. I have tried the modify method, but it does
not work on the xmlns attribute.
I can transform the field to nText and then parse this out, but I would
perfer to avoid any extra steps since it is a large quantity of data
and some of these xml fields are quite large.
I would be grateful for any advice on how to handle this.
Thanks in advance.
Rich FHave you tried to search using a namespace wildcard?
Like /*.foo/*:attr
namespace declarations are not exposed as attribute and cannot easily be
changed. If you need to change arbitrary namespaces to a specific namespace
you should probably write an XSLT transform.
Best regards
Michael
<eastegg_1970@.yahoo.com> wrote in message
news:1164911773.276725.95190@.j72g2000cwa.googlegroups.com...
> Hello All:
> I have a fair amount of SQL 2000 experience and recentlty have begun
> working with SQL 2005. SPecificall, I am working with the XML data
> type in the following scenario:
> -A third party vendor provides us with a data source containing a
> column of datatype XML. I would like to search through the nodes of
> the XML for specific elements and attributes. The problem is that some
> of the XML fields contain namespaces (meaning that my xQuery must
> declare the namespace as well), and some of the values in this same XML
> column do not have namespaces declared. I have no control over the
> data sent to me, but what I would like to do is remove the namespace
> attribute where it exists. I have tried the modify method, but it does
> not work on the xmlns attribute.
> I can transform the field to nText and then parse this out, but I would
> perfer to avoid any extra steps since it is a large quantity of data
> and some of these xml fields are quite large.
> I would be grateful for any advice on how to handle this.
> Thanks in advance.
> Rich F
>

Manipulating Namespace Attributes

Hello All:
I have a fair amount of SQL 2000 experience and recentlty have begun
working with SQL 2005. SPecificall, I am working with the XML data
type in the following scenario:
-A third party vendor provides us with a data source containing a
column of datatype XML. I would like to search through the nodes of
the XML for specific elements and attributes. The problem is that some
of the XML fields contain namespaces (meaning that my xQuery must
declare the namespace as well), and some of the values in this same XML
column do not have namespaces declared. I have no control over the
data sent to me, but what I would like to do is remove the namespace
attribute where it exists. I have tried the modify method, but it does
not work on the xmlns attribute.
I can transform the field to nText and then parse this out, but I would
perfer to avoid any extra steps since it is a large quantity of data
and some of these xml fields are quite large.
I would be grateful for any advice on how to handle this.
Thanks in advance.
Rich F
Have you tried to search using a namespace wildcard?
Like /*.foo/*:attr
namespace declarations are not exposed as attribute and cannot easily be
changed. If you need to change arbitrary namespaces to a specific namespace
you should probably write an XSLT transform.
Best regards
Michael
<eastegg_1970@.yahoo.com> wrote in message
news:1164911773.276725.95190@.j72g2000cwa.googlegro ups.com...
> Hello All:
> I have a fair amount of SQL 2000 experience and recentlty have begun
> working with SQL 2005. SPecificall, I am working with the XML data
> type in the following scenario:
> -A third party vendor provides us with a data source containing a
> column of datatype XML. I would like to search through the nodes of
> the XML for specific elements and attributes. The problem is that some
> of the XML fields contain namespaces (meaning that my xQuery must
> declare the namespace as well), and some of the values in this same XML
> column do not have namespaces declared. I have no control over the
> data sent to me, but what I would like to do is remove the namespace
> attribute where it exists. I have tried the modify method, but it does
> not work on the xmlns attribute.
> I can transform the field to nText and then parse this out, but I would
> perfer to avoid any extra steps since it is a large quantity of data
> and some of these xml fields are quite large.
> I would be grateful for any advice on how to handle this.
> Thanks in advance.
> Rich F
>

Managing ntext, text with a long text data

Hi,
I have a problem to insert(update) a long text (more than 64K) into
SQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.
MSDN says: "When the ntext, text, and image data values get larger,
however, they must be handled on a block-by-block basis. Both
Transact-
SQL and the database APIs contain functions that allow applications to

work with ntext, text, and image data block by block." Could somebody

give me an example how to do this, please.
Thank youThere are examples under UPDATETEXT and WRITETEXT in Books Online - do
these cover what you're trying to do? The MSSQL Resource Kit also has a
whole chapter on working with BLOBs, including a number of examples
using TSQL and ADO:

http://www.microsoft.com/technet/pr...art3/c1161.mspx

Simon|||igorsl (igorsl@.yahoo-dot-com.no-spam.invalid) writes:
> I have a problem to insert(update) a long text (more than 64K) into
> SQL 2000 (datatype - 'text'). It cuts the data and insert only 64K.
> MSDN says: "When the ntext, text, and image data values get larger,
> however, they must be handled on a block-by-block basis. Both
> Transact-
> SQL and the database APIs contain functions that allow applications to
> work with ntext, text, and image data block by block." Could somebody
> give me an example how to do this, please.

I believe this limitation is in the client API rather than in T-SQL
itself. (Altough inserting a 1MB value through a plain INSERT is not
that performant.) Which API are you using?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp