XML FeedsIf you have a bunch of User-Defined Data Types in your databases and you would like to get a list of them then you can run the following query
On SQL Server 2000 and up
- select * from systypes
- where xusertype > 256
or
On SQL Server 2005 and up
- SELECT * FROM sys.Types
- WHERE is_user_defined = 1
Let's take a look how this works by adding a couple of User-Defined Data Types. we will add a birthday type which will be a datetime (on SQL Server 2008 it should be a date) and a StateCode which is a char(2)
- USE master
- EXEC sp_addtype birthday, datetime, 'NULL'
- Go
- EXEC sp_addtype StateCode,'Char(2)' , 'NULL'
- Go
Now we can create a table which uses these two types
- create table TestType (Birthday birthday,State StateCode)
Insert some data
- insert TestType values('19100101','NY')
- insert TestType values('19800101','CA')
And we can query the data as usual
- select * from TestType
To see what data type is actually used to store the data we can run the following query
- select column_name,data_type,character_maximum_length
- from information_schema.columns
- where table_name = 'TestType'
output
----------------- column_name data_type character_maximum_length Birthday datetime State char 2
As you can see datetime and char(2) are used
We can also use the SysTypes (SQL Server 2000 and up) and Sys.Types system tables/catalog views
- SELECT s.name,s2.name,s2.length
- FROM SysTypes s
- join SysTypes s2 on s.xtype = s2.xtype
- WHERE s.xusertype > 256
- and s2.xusertype <= 256
output
---------------------- name name length birthday datetime 8 StateCode char 8000
- SELECT s.name,s2.name,s2.max_length
- FROM Sys.Types s
- join Sys.Types s2 on s2.user_type_id = s.system_type_id
- WHERE s.is_user_defined = 1
- and s2.is_user_defined = 0
output
---------------------- name name length birthday datetime 8 StateCode char 8000
How do you drop a User-Defined Data Type?
Here is how you do it. Run the following query
- USE master
- EXEC sp_droptype 'birthday'
- GO
As you can see you get the following error
Server: Msg 15180, Level 16, State 1, Procedure sp_droptype, Line 32
Cannot drop. The data type is being used.
So we first need to drop the table that is using this data type
- drop table TestType
Now we can try again
- USE master
- EXEC sp_droptype 'StateCode'
- USE master
- EXEC sp_droptype 'birthday'
And that drops the User-Defined Data Types we created
*** If you have a SQL related question try our Microsoft SQL Server Programming forum or our Microsoft SQL Server Admin forum
Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.