If 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