Get sql text of a UDT

Question

When creating a function or a trigger, you can find its creation command in sys.sql_modules

But what i cannot seem to find is the same for UDTs.

When I create a type like this

CREATE TYPE dbo.UserIds AS TABLE (value int NOT NULL PRIMARY KEY);

it doesnt show up in sys.sql_modules.

The only information I can find is from sys.types

select * from sys.types where is_user_defined = 1

but there is no SQL statement. So where do i find it?

Answer 1

You could get info about table type and columns from metadata:

CREATE TYPE dbo.UserIds AS TABLE (value int NOT NULL PRIMARY KEY);

--
DECLARE @name SYSNAME = 'UserIds';

SELECT  sch = SCHEMA_NAME(t.schema_id)
     ,t.name
     ,type     = TYPE_NAME(t.user_type_id)
     ,c.name
     ,col_type = TYPE_NAME(c.user_type_id)
     ,c.is_nullable
     --.... more info about max_length (if text) and so on
FROM sys.table_types t
JOIN sys.columns c
  ON t.type_table_object_id = c.object_id
WHERE t.name = @name;

Based on that info you could recreate DDL statement.

EDIT:

If you want to script it the easiest way is to use SSMS. Right-click -> Script object as (same for simple types).