How do I list user defined types in a SQL Server database?

Question

I need to enumerate all the user defined types created in a SQL Server database with CREATE TYPE, and/or find out whether they have already been defined.

With tables or stored procedures I'd do something like this:

if exists (select * from dbo.sysobjects where name='foobar' and xtype='U')
    drop table foobar

However I can't find the equivalent (or a suitable alternative) for user defined types! I definitely can't see them anywhere in sysobjects. Can anyone enlighten me?

Answer 1

Types and UDTs don't appear in sys.objects. You should be able to get what you're looking for with the following:

select * from sys.types
where is_user_defined = 1