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

jammycakes_886 0
Viewed 62471

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?

tsql sql-server sql
3 Answers

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
jwolly2_52020

To expand on jwolly2's answer, here's how you get a list of definitions including the standard data type:

-- User Defined Type definitions TP 20180124
select t1.name, t2.name, t1.precision, t1.scale, t1.max_length as bytes, t1.is_nullable
from sys.types t1
join sys.types t2 on t2.system_type_id = t1.system_type_id and t2.is_user_defined = 0
where t1.is_user_defined = 1 and t2.name <> 'sysname'
order by t1.name
user_44907060
Related