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

<p>With tables or stored procedures I'd do something like this:</p>

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

<p>However I can't find the equivalent (or a suitable alternative) for user defined types! I definitely can't see them anywhere in <code>sysobjects</code>. </p>

<p>Can anyone enlighten me?</p>

[{'answer_id': 54496, 'author': 'jwolly2', 'author_id': 5202, 'author_profile': 'https://Stackoverflow.com/users/5202', 'pm_score': 7, 'selected': True, 'text': "<p>Types and UDTs don't appear in sys.objects.\nYou should be able to get what you're looking for with the following:</p>\n\n<pre><code>select * from sys.types\nwhere is_user_defined = 1\n</code></pre>\n"}, {'answer_id': 31549846, 'author': 'Ron Sanderson', 'author_id': 3034283, 'author_profile': 'https://Stackoverflow.com/users/3034283', 'pm_score': 4, 'selected': False, 'text': '<p>Although the post is old, I found it useful to use a query similar to this. \nYou may not find some of the formatting useful, but I wanted the fully qualified type name and I wanted to see the columns listed in order. You can just remove all of the SUBSTRING stuff to just get the column name by itself.</p>\n\n<pre><code>SELECT USER_NAME(TYPE.schema_id) + \'.\' + TYPE.name      AS "Type Name",\n       COL.column_id,\n       SUBSTRING(CAST(COL.column_id + 100 AS char(3)), 2, 2)  + \': \' + COL.name   AS "Column",\n       ST.name                                          AS "Data Type",\n       CASE COL.Is_Nullable\n       WHEN 1 THEN \'\'\n       ELSE        \'NOT NULL\' \n       END                                              AS "Nullable",\n       COL.max_length                                   AS "Length",\n       COL.[precision]                                  AS "Precision",\n       COL.scale                                        AS "Scale",\n       ST.collation                                     AS "Collation"\nFROM sys.table_types TYPE\nJOIN sys.columns     COL\n    ON TYPE.type_table_object_id = COL.object_id\nJOIN sys.systypes AS ST  \n    ON ST.xtype = COL.system_type_id\nwhere TYPE.is_user_defined = 1\nORDER BY "Type Name",\n         COL.column_id\n</code></pre>\n'}, {'answer_id': 48426414, 'author': 'Ton Plooij', 'author_id': 4490706, 'author_profile': 'https://Stackoverflow.com/users/4490706', 'pm_score': 3, 'selected': False, 'text': "<p>To expand on jwolly2's answer, here's how you get a list of definitions including the standard data type:</p>\n\n<pre><code>-- User Defined Type definitions TP 20180124\nselect t1.name, t2.name, t1.precision, t1.scale, t1.max_length as bytes, t1.is_nullable\nfrom sys.types t1\njoin sys.types t2 on t2.system_type_id = t1.system_type_id and t2.is_user_defined = 0\nwhere t1.is_user_defined = 1 and t2.name &lt;&gt; 'sysname'\norder by t1.name\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/54482', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/886/']