How to tell if systype is user defined

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tshad - 21 Jul 2008 18:44 GMT

I ran into a problem with my query where I was trying to get the type and
length of fields in my table.

But I ran into a problem with varchars and chars passing back multiple
values from the types table.

I was using something like this in my select list to get length:

SELECT st.Length
FROM syscolumns sc
JOIN systypes st on (sc.xtype = st.xtype)
WHERE (sc.name = 'Createdby') AND id = 20299232

It works fine with datetimes, and ints, but was giving me multiple results
from varchar.

I found out that it was from User Types.

bigint 127
varbinary 165
varchar 167
firstname 167
intcityprovince 167
phone_extension 167
postalcode 167
address 167
city 167
email 167
lastname 167
salutation 167
password 167
binary 173
char 175
zip 175
middleinitial 175

You can see that there are multiple xtype 167 and 175. These are user
types.

I can differentiate between them by also using uid from the systypes table.
This is the User ID of data type creator. As it turns out it is either a 4
or a 1. Not sure what that means but if I change the test to only look at
uid = 4 than it works fine.

Is uid always 4 for non-user types?

Thanks,

Tom

Reply to this Message

Aaron Bertrand [SQL Server MVP] - 21 Jul 2008 19:17 GMT

I don't understand, what result are you getting, and what do you want
instead?

In SQL Server 2005 you can differentiate by querying from sys.types and
comparing system_type_id with user_type_id. These will be different for
alias types you have defined, like salutation, lastname etc. (There is one
exception: sysname, which is a built-in alias type.)

But do you really want to filter out these alias types? Why? They can be
used in your tables just like the built-in types, but can have different
lengths.

On 7/21/08 1:44 PM, in article uX8myl16IHA.1468@TK2MSFTNGP05.phx.gbl,

> I ran into a problem with my query where I was trying to get the type and
> length of fields in my table.
[quoted text clipped - 45 lines]
>
> Tom

Reply to this Message

Plamen Ratchev - 21 Jul 2008 19:30 GMT

I believe it is the same logic when using systypes, when 'xtype' and
'xusertype' are different it is user defined, with exception of sysnames.

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

tshad - 21 Jul 2008 21:27 GMT

>I believe it is the same logic when using systypes, when 'xtype' and
>'xusertype' are different it is user defined, with exception of sysnames.

So the question washow was I going to get the correct systype name from a
syscolumn row?

Can't use xtype to find it. It turned out that I could use xusertype which
is the same as xtype except for user types. User type rows will be unique
and the xusertype field from syscolumns will match the xusertype field from
systypes.

Now I can get the user type name as well.

Thanks,

Tom

> Plamen Ratchev
> http://www.SQLStudio.com

Reply to this Message

Plamen Ratchev - 21 Jul 2008 21:40 GMT

See my other post, you can use the TYPE_NAME function:

SELECT TYPE_NAME(xtype)
FROM syscolumns;

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

tshad - 21 Jul 2008 21:03 GMT

>I don't understand, what result are you getting, and what do you want
> instead?

> In SQL Server 2005 you can differentiate by querying from sys.types and
> comparing system_type_id with user_type_id. These will be different for
[quoted text clipped - 61 lines]
>>
>> Tom

Reply to this Message

tshad - 21 Jul 2008 21:07 GMT

Sorry I accitdently hit the wrong keys and it got sent:

"tshad" <tshad@dslextreme.com> wrote in message news:...

> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in
> message news:C4AA4AE5.D018%ten.xoc@dnartreb.noraa...
>>I don't understand, what result are you getting, and what do you want
>> instead?

What I am getting is an error, because I have an object from syscolumns,
and
I am trying to get the "type" name and "length". The problem is that if
the
"type" id from syscolumns is 167 (varchar, firstname, postalcode, city
etc),
I get an error on my select because it can only return one value. But for
167, I have 11 values. And for 175, I have 3.

So if I am looking for the name of the type:

Select a.Name,a.id,Records,
CreatedByName = (SELECT st.name
FROM syscolumns sc
JOIN systypes st ON (sc.xtype = st.xtype)
WHERE (sc.name = 'CreatedBy') AND a.id = sc.id),
FROM (select Object_Name(id) Name,id,count(*) as Records
from syscolumns
where name = 'CreatedBy'
group by Object_Name(id),id) as a
order by Name

this gives me an error if "CreatedBy" is a varhar (167) or a char (175)
since you can't have multiple rows return on this type of subquery (the one
that returns CreateByName.

But if I change it to:

Select a.Name,a.id,Records,
CreatedByName = (SELECT st.name
FROM syscolumns sc
JOIN systypes st ON (sc.xtype = st.xtype)
WHERE (sc.name = 'CreatedBy') AND uid = 4 AND a.id = sc.id),
FROM (select Object_Name(id) Name,id,count(*) as Records
from syscolumns
where name = 'CreatedBy'
group by Object_Name(id),id
having count(*) = 5) as a
order by Name

where I add a filter (uid = 4), it works fine - but won't give me the user
types - since they are all 167 but only one has uid of 4. The rest are 1.

This is fine for what I am doing at the moment. But it would be nice to be
able to get the correctly type name - I just can't do it this way.

> In SQL Server 2005 you can differentiate by querying from sys.types and
> comparing system_type_id with user_type_id. These will be different for
[quoted text clipped - 6 lines]
>> used in your tables just like the built-in types, but can have different
>> lengths.

Yes, in this case.

Thanks,

Tom

>> On 7/21/08 1:44 PM, in article uX8myl16IHA.1468@TK2MSFTNGP05.phx.gbl,
>> "tshad" <tshad@dslextreme.com> wrote:
[quoted text clipped - 53 lines]
>>>
>>> Tom

Reply to this Message