CODEWITHSUNDEEP
sqlsql-servert-sql
jammycakes
jammycakes

Reputation: 5866

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

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?

Upvotes: 45

Views: 79117

Answers (3)

Ton Plooij
Ton Plooij

Reputation: 2641

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];

Upvotes: 5

Ron Sanderson
Ron Sanderson

Reputation: 183

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

Upvotes: 18

jwolly2
jwolly2

Reputation: 1206

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

Upvotes: 95

Related Questions