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

T-SQL
1
2
select * from systypes
where xusertype > 256
select * from systypes
where xusertype > 256

or

On SQL Server 2005 and up

T-SQL
1
2
SELECT * FROM sys.Types 
WHERE is_user_defined = 1
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)

T-SQL
1
2
3
4
5
USE master
EXEC sp_addtype birthday, datetime, 'NULL'
Go
EXEC sp_addtype StateCode,'Char(2)' , 'NULL'
Go
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

T-SQL
1
create table TestType (Birthday birthday,State StateCode)
create table TestType (Birthday birthday,State StateCode)

Insert some data

T-SQL
1
2
insert TestType values('19100101','NY')
insert TestType values('19800101','CA')
insert TestType values('19100101','NY')
insert TestType values('19800101','CA')

And we can query the data as usual

T-SQL
1
select * from TestType
select * from TestType

To see what data type is actually used to store the data we can run the following query

T-SQL
1
2
3
select column_name,data_type,character_maximum_length
from information_schema.columns
where table_name = 'TestType'
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

T-SQL
1
2
3
4
5
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
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
T-SQL
1
2
3
4
5
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
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

T-SQL
1
2
3
USE master
EXEC sp_droptype 'birthday'
GO
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

T-SQL
1
drop table TestType
drop table TestType

Now we can try again

T-SQL
1
2
3
4
5
6
7
USE master
EXEC sp_droptype 'StateCode'
 
 
 
USE master
EXEC sp_droptype 'birthday'
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