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.

I don't understand what you are asking? I assume not the regular create as per the [docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-type-transact-sql?view=sql-server-ver15)? — Dale K, Oct 22 '19 at 05:57
You could refer this : https://www.mssqltips.com/sqlservertip/1628/sql-server-user-defined-data-types-rules-and-defaults/ — CaffeinatedCod3r, Oct 22 '19 at 06:13
So you're trying to duplicate a UDT from one database to another, is that correct? You're looking to generate SQL from an existing UDT that you can execute against another DB? — AlwaysLearning, Oct 22 '19 at 06:16
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). I updated the title, question, and tags to reflect that you are really asking about UDDTs, not UDTs. — Solomon Rutzky, Oct 22 '19 at 07:01

Solomon Rutzky · Accepted Answer · 2019-10-22T18:16:11.203

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';.

@DmitrijKultasev ok, added info for Rules and Defaults. It is _highly_ unlikely that either are used, so I need to re-focus on stuff that I am behind on...if you need them, the info provided (below the example code) should be enough to get them. Good luck! — Solomon Rutzky, Oct 22 '19 at 18:18
how do you know that they are deprecated? I've tried to google and didn't find any official documentation that would state that, anyway, thanks — Dmitrij Kultasev, Oct 23 '19 at 04:49
@DmitrijKultasev You're quite welcome. Re: deprecated Rules and Defaults, why look at Google? Just look at the documentation that I linked for each one. There's a banner at the top of each page saying: "_**Important** This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature._" Also see: https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008/ms143729%28v%3dsql.100%29#features-not-supported-in-a-future-version-of-sql-server — Solomon Rutzky, Oct 23 '19 at 05:17

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

1 Answers1