September 14, 2006 at 9:30 am
Hi there,
I understand the SYSTYPES table contains both system- as user-defined datatypes. I am trying to select the UDD's from this table, but sofar I cannot recognize the differences between a UDD and a syatem datatype. I could set op something like
WHERE NOT IN(....
and then list all system-datatype names, but I would hope there is a smarter way of selecting UDDs...
Greetz,
Hans Brouwer
September 14, 2006 at 9:40 am
Hello Hans,
Can you check this query:
select
* from sys.systypes where uid != 4
Hope this helps you.
Thanks
Lucky
September 14, 2006 at 10:07 pm
Or to use the SQL 2005 sys view, simply look at the is_user_defined column of the sys.types view. From Books online:
is_user_defined | bit | 1 = User-defined type. 0 = SQL Server 2005 system data type. |
September 15, 2006 at 12:33 am
Tnx for responses.
Lucky, I have tried your statement, but it returnes everything, since no uid = 4 to begin with. When adding a UDD and checking this in systypes, I see no different uid then for the existing system datatypes.
Do I miss something here?
Greetz,
Hans Brouwer
September 15, 2006 at 11:38 am
Since you are using SQL 2005, you should just use the SQL 2005 view and forget about the SQL 2000 compatibility view.
Select
*
From
sys.types
Where
is_user_defined = 1
September 16, 2006 at 1:57 am
Tnx Robert, however, we also have some SQL2000 servers. I am still at a loss how to finds the wretched thingies in that version...
Greetz,
Hans Brouwer
September 16, 2006 at 1:31 pm
try
select
* from systypes where xusertype > 256
September 19, 2006 at 1:10 am
Tnx Eugene, this does it. Quetion: how did you find out? Now that I know I can find out, but looking in BOL I could not find this info...
Greetz,
Hans Brouwer
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply