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?

Unipolar

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

Phosphorus

How can we get type's description (e.g. User Defined Table Type)? – Orthocephalic

@Orthocephalic You can open Sql Server Management Studio then 1.go to your DB 2. expand [Programmability] 3. expand [Types] 4. expand [User-Defined table types] & you will see a list of your types. See this image (i.stack.imgur.com/4HInu.png) for an example. After that, right-click the UDT and a menu will pop up. Select [Script User-Defined Table type as...] [Create to...] [New query editor] A window will appear with the DDL (data definition language) which will show all the types that this UDT encompasses. – Cortisone

Answer 2

Although the post is old, I found it useful to use a query similar to this. You 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.

SELECT USER_NAME(TYPE.schema_id) + '.' + TYPE.name      AS "Type Name",
       COL.column_id,
       SUBSTRING(CAST(COL.column_id + 100 AS char(3)), 2, 2)  + ': ' + COL.name   AS "Column",
       ST.name                                          AS "Data Type",
       CASE COL.Is_Nullable
       WHEN 1 THEN ''
       ELSE        'NOT NULL' 
       END                                              AS "Nullable",
       COL.max_length                                   AS "Length",
       COL.[precision]                                  AS "Precision",
       COL.scale                                        AS "Scale",
       ST.collation                                     AS "Collation"
FROM sys.table_types TYPE
JOIN sys.columns     COL
    ON TYPE.type_table_object_id = COL.object_id
JOIN sys.systypes AS ST  
    ON ST.xtype = COL.system_type_id
where TYPE.is_user_defined = 1
ORDER BY "Type Name",
         COL.column_id

Hesperides

Answer 3

Original comment:

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

Edit in Comment:

I have just added an update to the Query aliasing/formatting to make the query more readable and updated the join key used to eliminate the need to filter out duplicate "system_type_id" values when "is_user_defined" = 0.

The idea here is that we can find information about types in the sys.types table.

1. When "is_user_defined" = 0, it is a built in type
2. When "system_type_id" matches "user_type_id" on the same record, it is a system type.
3. When "is_user_defined" = 1 the related system type will have the same "user_type_id" as the "system_type_id" on the user defined type
4. The "max_length" field refers to the max length in bytes (as opposed to characters - NVARCHAR(10) would be 20 / VARCHAR(10) would be 10)

Type Info Query:

SELECT  UserType.[name] AS UserType
        , SystemType.[name] AS SystemType
        , UserType.[precision]
        , UserType.scale
        , UserType.max_length AS bytes
            --This value indicates max number of bytes as opposed to max length in characters
            -- NVARCHAR(10) would be 20 / VARCHAR(10) would be 10
        , UserType.is_nullable
  FROM  sys.types UserType
      JOIN sys.types SystemType
          ON SystemType.user_type_id = UserType.system_type_id
             AND SystemType.is_user_defined = 0
 WHERE  UserType.is_user_defined = 1
 ORDER BY UserType.[name];

Grantham

Why t2.name <> 'sysname' – Huarache

@MuraliDharDarshan nvarchar and sysname share the system_type_id 231 More accurately we should be joining system_type_id from the t1(user defined type table) on user_type_id from the t2 (source type) table rather than excluding 'sysname' – Struve