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

Question

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

With tables or stored procedures I would do something like this:

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

Still, I can't find the equivalent (or a suitable alternative) for user-defined types. I surely can not see them anywhere in sysobjects.

Can anyone help me out regarding this?

Answer 1

Types and UDTs do not appear in sys.objects. You could be able to get what you are looking for using the below code:

select * from sys.types
where is_user_defined = 1

Want to be a SQL expert? Come and join this SQL Certification course by Intellipaat.

You can check out the below SQL Tutorial video for better understanding.