Programmatically generate DDL statement for User Defined Data Type (UDDT)

Question

I'm trying to add faking synonyms pointing to remote objects support(PR) for tSQLt.

To make it possible to mock/fake tables that have user defined system types in the table definitions, I need to generate the User Defined Data Type (UDDT) DDL statement.

I don't have too much experience with UDDTs, and what I can think of about very simple scenario if the UDDT just is as simple if it would be something like CREATE TYPE dbo.SomeType FROM INT, but I know that it can be much more complex.

So, the question is if anyone has some working solution already, ideally implemented using T-SQL via system objects. In the worst case, SQLCLR would be the only other option.

Answer 1

There are different kinds of User-Defined Types: User-Defined Table Types (UDTT), User-Defined Types (UDT; complex types implemented via SQLCLR), and User-Defined Data Types (UDDT; mainly synonyms of existing system types, but with size/precision and NULL / NOT NULL included).

You don't need to worry about UDTTs as they cannot be columns in tables.

You probably can't deal with UDTs, at least not right now, as that adds a lot of complexity since you would need to copy the assembly as well.

There is one main system catalog view: sys.types. The following should get you most, if not all, of what you need. collation_name does not appear to be usable, either by testing a simple CREATE TYPE as a test, or the documentation.

SELECT N'CREATE TYPE ' + QUOTENAME(sch.[name])
         + N'.' + QUOTENAME(typ.[name])
         + N' FROM ' + styp.[name]
         + CASE
             WHEN typ.[system_type_id] IN
                       (41, 42, 43, 106, 108, 165, 167, 173, 175, 231, 239)
               THEN N'('
                    + CASE
                        WHEN typ.[max_length] = -1 -- for: VARCHAR, NVARCHAR, VARBINARY
                          THEN N'MAX'
                        WHEN typ.[system_type_id] IN (165, 167, 173, 175)
                          -- VARBINARY, VARCHAR, BINARY, CHAR
                          THEN CONVERT(NVARCHAR(5), typ.[max_length])
                        WHEN typ.[system_type_id] IN (231, 239) -- NVARCHAR, NCHAR
                          THEN CONVERT(NVARCHAR(5), (typ.[max_length] / 2))
                        WHEN typ.[system_type_id] IN (41, 42, 43)
                          -- TIME, DATETIME2, DATETIMEOFFSET
                          THEN CONVERT(NVARCHAR(5), typ.[scale])
                        WHEN typ.[system_type_id] IN (106, 108) -- DECIMAL, NUMERIC
                          THEN CONVERT(NVARCHAR(5), typ.[precision]) 
                               + N', ' + CONVERT(NVARCHAR(5), typ.[scale])
                        END            
                    + N')'
             ELSE N''
           END
         + CASE typ.[is_nullable] WHEN 1 THEN N' NULL' ELSE ' NOT NULL' END
         + N';'
FROM   sys.types typ
INNER JOIN sys.schemas sch
        ON sch.[schema_id] = typ.[schema_id]
INNER JOIN sys.types styp
        ON styp.[user_type_id] = typ.[system_type_id]
WHERE  typ.[is_user_defined] = 1
AND    typ.[is_assembly_type] = 0
AND    typ.[is_table_type] = 0;

Rules

!! These have been deprecated for a long time and should not be used !!

Documentation for CREATE RULE

Rules can be found in: sys.sql_modules (which includes the CREATE statement in the [definition] field)

You will need to separately cycle through sys.types and, for any user-created types where rule_object_id <> 0, execute EXEC sp_bindrule N'@rulename', N'@objectname';.

Defaults

!! These have been deprecated for a long time and should not be used !!

Documentation for CREATE DEFAULT

Defaults can be found in: sys.sql_modules (which includes the CREATE statement in the [definition] field)

You will need to separately cycle through sys.types and, for any user-created types where default_object_id <> 0, execute EXEC sp_bindefault N'@defaultname', N'@objectname';.