Script out all user defined table type in SQL Server

sourav dutta 201 Reputation points
2020-10-06T12:46:04.467+00:00

How to script out all user defined table type in SQL Server.

Thanks in Advance

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,513 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2020-10-07T08:55:50.827+00:00

    Hi @sourav dutta ,

    Please find the updated complete query from below:

    SELECT   
           sch.name AS UDT_SCHEMA_NAME   
          ,userDefinedTypes.name AS UDT_TYPE_NAME   
         ,  
         N'IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N''' + REPLACE(userDefinedTypes.name, '''', '''''') + N''' AND ss.name = N''' + REPLACE(sch.name, '''', '''''') + N''') '  
         + NCHAR(13) + NCHAR(10)   
         +  
         CASE WHEN userDefinedTypeProperties.IsTableType = 1   
             THEN N'CREATE TYPE ' + QUOTENAME(sch.name) + '.' + QUOTENAME(userDefinedTypes.name) + ' AS TABLE (   
         ' + tAllColumns.column_definition  + N'  
     ); '   
             ELSE   
                 + N'CREATE TYPE ' + QUOTENAME(sch.name) + '.' + QUOTENAME(userDefinedTypes.name)   
                 + N' FROM '   
                 + tBaseTypeComputation.baseTypeName   
                 + CASE WHEN userDefinedTypeProperties.is_nullable = 0 THEN N' NOT NULL' ELSE N'' END   
                 + N'; '   
         END AS SqlCreateUdt  
          
     FROM sys.types AS userDefinedTypes   
          
     INNER JOIN sys.schemas AS sch   
         ON sch.schema_id = userDefinedTypes.schema_id   
          
     LEFT JOIN sys.table_types AS userDefinedTableTypes   
         ON userDefinedTableTypes.user_type_id = userDefinedTypes.user_type_id   
          
     LEFT JOIN sys.types AS systemType   
         ON systemType.system_type_id = userDefinedTypes.system_type_id   
         AND systemType.is_user_defined = 0   
          
     OUTER APPLY   
         (  
             SELECT   
                  userDefinedTypes.is_nullable   
                 ,userDefinedTypes.precision AS NUMERIC_PRECISION   
                 ,userDefinedTypes.scale AS NUMERIC_SCALE   
                 ,userDefinedTypes.max_length AS CHARACTER_MAXIMUM_LENGTH  
                 ,CASE WHEN userDefinedTableTypes.user_type_id IS NULL THEN 0 ELSE 1 END AS IsTableType   
                 ,CONVERT(smallint,   
                         CASE -- datetime/smalldatetime    
                         WHEN userDefinedTypes.system_type_id IN (40, 41, 42, 43, 58, 61) THEN ODBCSCALE(userDefinedTypes.system_type_id, userDefinedTypes.scale)   
                         END  
                 ) AS DATETIME_PRECISION   
         ) AS userDefinedTypeProperties   
          
          
     OUTER APPLY   
         (  
             SELECT   
                 systemType.name   
                 +   
                 CASE   
                     WHEN systemType.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary')   
                         THEN N'('   
                             +   
                             CASE WHEN userDefinedTypeProperties.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'  
                                 ELSE CONVERT  
                                     (  
                                         varchar(4)  
                                         ,userDefinedTypeProperties.CHARACTER_MAXIMUM_LENGTH   
                                     )  
                             END   
                             + N')'   
                     WHEN systemType.name IN ('decimal', 'numeric')  
                         THEN N'(' + CONVERT(varchar(4), userDefinedTypeProperties.NUMERIC_PRECISION) + N', ' + CONVERT(varchar(4), userDefinedTypeProperties.NUMERIC_SCALE) + N')'  
                     WHEN systemType.name IN ('time', 'datetime2', 'datetimeoffset')   
                         THEN N'(' + CAST(userDefinedTypeProperties.DATETIME_PRECISION AS national character varying(36)) + N')'   
                     ELSE N''   
                 END AS baseTypeName    
         ) AS tBaseTypeComputation   
          
     OUTER APPLY   
         (  
             SELECT   
                 (  
                     SELECT   
                         -- ,clmns.is_nullable   
                         -- ,tComputedProperties.ORDINAL_POSITION  
                         -- ,tComputedProperties.COLUMN_DEFAULT  
          
                           CASE WHEN tComputedProperties.ORDINAL_POSITION = 1 THEN N' ' ELSE N',' END   
                         + QUOTENAME(clmns.name)   
                         + N' '  
                         + tComputedProperties.DATA_TYPE   
                         +   
                         CASE   
                             WHEN tComputedProperties.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary')   
                                 THEN N'('   
                                     +   
                                     CASE WHEN tComputedProperties.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'  
                                         ELSE CONVERT  
                                             (  
                                                 varchar(4)  
                                                 ,tComputedProperties.CHARACTER_MAXIMUM_LENGTH   
                                             )  
                                     END   
                                     + N')'   
                             WHEN tComputedProperties.DATA_TYPE IN ('decimal', 'numeric')  
                                 THEN N'(' + CONVERT(varchar(4), tComputedProperties.NUMERIC_PRECISION) + N', ' + CONVERT(varchar(4), tComputedProperties.NUMERIC_SCALE) + N')'  
                             WHEN tComputedProperties.DATA_TYPE IN ('time', 'datetime2', 'datetimeoffset')   
                                 THEN N'(' + CAST(tComputedProperties.DATETIME_PRECISION AS national character varying(36)) + N')'   
                             ELSE N''   
                         END   
                         + CASE WHEN tComputedProperties.is_nullable = 0 THEN N'  NOT NULL' ELSE N' NULL' END   
                         + NCHAR(13) + NCHAR(10)   
                         AS [text()]  
                     FROM sys.columns AS clmns   
                     INNER JOIN sys.types AS t ON t.system_type_id = clmns.system_type_id   
                     LEFT JOIN sys.types ut ON ut.user_type_id = clmns.user_type_id   
          
                     OUTER APPLY   
                         (  
                             SELECT   
                                  33 As bb   
                                 ,COLUMNPROPERTY(clmns.object_id, clmns.name, 'ordinal')  AS ORDINAL_POSITION   
                                 ,COLUMNPROPERTY(clmns.object_id, clmns.name, 'charmaxlen') AS CHARACTER_MAXIMUM_LENGTH   
                                 ,COLUMNPROPERTY(clmns.object_id, clmns.name, 'octetmaxlen') AS CHARACTER_OCTET_LENGTH   
                                 ,CONVERT(nvarchar(4000), OBJECT_DEFINITION(clmns.default_object_id)) AS COLUMN_DEFAULT   
          
                                 ,clmns.is_nullable   
                                 ,t.name AS DATA_TYPE  
          
                                 ,CONVERT(tinyint,   
                                     CASE -- int/decimal/numeric/real/float/money    
                                         WHEN clmns.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) THEN clmns.precision    
                                     END  
                                  ) AS NUMERIC_PRECISION   
          
                                 ,CONVERT(int,   
                                     CASE -- datetime/smalldatetime    
                                         WHEN clmns.system_type_id IN (40, 41, 42, 43, 58, 61) THEN NULL    
                                         ELSE ODBCSCALE(clmns.system_type_id, clmns.scale)   
                                     END  
                                  ) AS NUMERIC_SCALE  
          
                                 ,CONVERT(smallint,   
                                      CASE -- datetime/smalldatetime    
                                         WHEN clmns.system_type_id IN (40, 41, 42, 43, 58, 61) THEN ODBCSCALE(clmns.system_type_id, clmns.scale)   
                                      END  
                                 ) AS DATETIME_PRECISION   
                         ) AS tComputedProperties    
          
                     WHERE clmns.object_id = userDefinedTableTypes.type_table_object_id   
      and tComputedProperties.DATA_TYPE<>'sysname'  
                     ORDER BY tComputedProperties.ORDINAL_POSITION   
          
                     FOR XML PATH(''), TYPE   
                 ).value('.', 'nvarchar(MAX)') AS column_definition   
         ) AS tAllColumns    
          
     WHERE userDefinedTypes.is_user_defined = 1   
    

    Output:
    IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'udt_dealer_distributor_mapping' AND ss.name = N'dbo') CREATE TYPE [dbo].[udt_dealer_distributor_mapping] AS TABLE ( [customer_name] varchar(MAX) NOT NULL ,[customer_address] varchar(MAX) NOT NULL ,[city] nvarchar(200) NOT NULL ,[contact_person] varchar(100) NOT NULL ,[contact_no1] nvarchar(200) NOT NULL ,[tax_number] nvarchar(200) NOT NULL ,[typename] varchar(100) NOT NULL ,[Parent_customer_code] nvarchar(200) NOT NULL ,[rate_code] varchar(100) NOT NULL ,[discount_code] nvarchar(200) NOT NULL );
    IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'ProductID' AND ss.name = N'dbo') CREATE TYPE [dbo].[ProductID] AS TABLE ( [item_id] int NULL );

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 additional answers

Sort by: Most helpful
  1. Erland Sommarskog 98,751 Reputation points
    2020-10-06T22:13:42.237+00:00

    Press F7 to get to Object Explorer Details. Navigate to your database, then Programmability, Types and User-defined table types. When you have opened this node, press Cltrl-A to select all. Then select Script to from the context menu.

    1 person found this answer helpful.
    0 comments No comments

  2. MelissaMa-MSFT 24,176 Reputation points
    2020-10-07T01:58:16.063+00:00

    Hi @sourav dutta ,

    Please refer the query in the answer in below link for more details:
    how to script out the user defined table types?

    I created one user defined table type from my side and it is working using that query.

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  3. MelissaMa-MSFT 24,176 Reputation points
    2020-10-07T06:39:53.237+00:00

    Hi @sourav dutta ,

    Please find the updated complete query from below:

    SELECT   
           sch.name AS UDT_SCHEMA_NAME   
          ,userDefinedTypes.name AS UDT_TYPE_NAME   
         ,  
         N'IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N''' + REPLACE(userDefinedTypes.name, '''', '''''') + N''' AND ss.name = N''' + REPLACE(sch.name, '''', '''''') + N''') '  
         + NCHAR(13) + NCHAR(10)   
         +  
         CASE WHEN userDefinedTypeProperties.IsTableType = 1   
             THEN N'CREATE TYPE ' + QUOTENAME(sch.name) + '.' + QUOTENAME(userDefinedTypes.name) + ' AS TABLE (   
         ' + tAllColumns.column_definition  + N'  
     ); '   
             ELSE   
                 + N'CREATE TYPE ' + QUOTENAME(sch.name) + '.' + QUOTENAME(userDefinedTypes.name)   
                 + N' FROM '   
                 + tBaseTypeComputation.baseTypeName   
                 + CASE WHEN userDefinedTypeProperties.is_nullable = 0 THEN N' NOT NULL' ELSE N'' END   
                 + N'; '   
         END AS SqlCreateUdt  
          
     FROM sys.types AS userDefinedTypes   
          
     INNER JOIN sys.schemas AS sch   
         ON sch.schema_id = userDefinedTypes.schema_id   
          
     LEFT JOIN sys.table_types AS userDefinedTableTypes   
         ON userDefinedTableTypes.user_type_id = userDefinedTypes.user_type_id   
          
     LEFT JOIN sys.types AS systemType   
         ON systemType.system_type_id = userDefinedTypes.system_type_id   
         AND systemType.is_user_defined = 0   
          
     OUTER APPLY   
         (  
             SELECT   
                  userDefinedTypes.is_nullable   
                 ,userDefinedTypes.precision AS NUMERIC_PRECISION   
                 ,userDefinedTypes.scale AS NUMERIC_SCALE   
                 ,userDefinedTypes.max_length AS CHARACTER_MAXIMUM_LENGTH  
                 ,CASE WHEN userDefinedTableTypes.user_type_id IS NULL THEN 0 ELSE 1 END AS IsTableType   
                 ,CONVERT(smallint,   
                         CASE -- datetime/smalldatetime    
                         WHEN userDefinedTypes.system_type_id IN (40, 41, 42, 43, 58, 61) THEN ODBCSCALE(userDefinedTypes.system_type_id, userDefinedTypes.scale)   
                         END  
                 ) AS DATETIME_PRECISION   
         ) AS userDefinedTypeProperties   
          
          
     OUTER APPLY   
         (  
             SELECT   
                 systemType.name   
                 +   
                 CASE   
                     WHEN systemType.name IN ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary')   
                         THEN N'('   
                             +   
                             CASE WHEN userDefinedTypeProperties.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'  
                                 ELSE CONVERT  
                                     (  
                                         varchar(4)  
                                         ,userDefinedTypeProperties.CHARACTER_MAXIMUM_LENGTH   
                                     )  
                             END   
                             + N')'   
                     WHEN systemType.name IN ('decimal', 'numeric')  
                         THEN N'(' + CONVERT(varchar(4), userDefinedTypeProperties.NUMERIC_PRECISION) + N', ' + CONVERT(varchar(4), userDefinedTypeProperties.NUMERIC_SCALE) + N')'  
                     WHEN systemType.name IN ('time', 'datetime2', 'datetimeoffset')   
                         THEN N'(' + CAST(userDefinedTypeProperties.DATETIME_PRECISION AS national character varying(36)) + N')'   
                     ELSE N''   
                 END AS baseTypeName    
         ) AS tBaseTypeComputation   
          
     OUTER APPLY   
         (  
             SELECT   
                 (  
                     SELECT   
                         -- ,clmns.is_nullable   
                         -- ,tComputedProperties.ORDINAL_POSITION  
                         -- ,tComputedProperties.COLUMN_DEFAULT  
          
                           CASE WHEN tComputedProperties.ORDINAL_POSITION = 1 THEN N' ' ELSE N',' END   
                         + QUOTENAME(clmns.name)   
                         + N' '  
                         + tComputedProperties.DATA_TYPE   
                         +   
                         CASE   
                             WHEN tComputedProperties.DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'binary', 'varbinary')   
                                 THEN N'('   
                                     +   
                                     CASE WHEN tComputedProperties.CHARACTER_MAXIMUM_LENGTH = -1 THEN 'MAX'  
                                         ELSE CONVERT  
                                             (  
                                                 varchar(4)  
                                                 ,tComputedProperties.CHARACTER_MAXIMUM_LENGTH   
                                             )  
                                     END   
                                     + N')'   
                             WHEN tComputedProperties.DATA_TYPE IN ('decimal', 'numeric')  
                                 THEN N'(' + CONVERT(varchar(4), tComputedProperties.NUMERIC_PRECISION) + N', ' + CONVERT(varchar(4), tComputedProperties.NUMERIC_SCALE) + N')'  
                             WHEN tComputedProperties.DATA_TYPE IN ('time', 'datetime2', 'datetimeoffset')   
                                 THEN N'(' + CAST(tComputedProperties.DATETIME_PRECISION AS national character varying(36)) + N')'   
                            -- ELSE N''   
                         END   
                         + CASE WHEN tComputedProperties.is_nullable = 0 THEN N'  NOT NULL' ELSE N' NULL' END   
                         + NCHAR(13) + NCHAR(10)   
                         AS [text()]  
                     FROM sys.columns AS clmns   
                     INNER JOIN sys.types AS t ON t.system_type_id = clmns.system_type_id   
                     LEFT JOIN sys.types ut ON ut.user_type_id = clmns.user_type_id   
          
                     OUTER APPLY   
                         (  
                             SELECT   
                                  33 As bb   
                                 ,COLUMNPROPERTY(clmns.object_id, clmns.name, 'ordinal')  AS ORDINAL_POSITION   
                                 ,COLUMNPROPERTY(clmns.object_id, clmns.name, 'charmaxlen') AS CHARACTER_MAXIMUM_LENGTH   
                                 ,COLUMNPROPERTY(clmns.object_id, clmns.name, 'octetmaxlen') AS CHARACTER_OCTET_LENGTH   
                                 ,CONVERT(nvarchar(4000), OBJECT_DEFINITION(clmns.default_object_id)) AS COLUMN_DEFAULT   
          
                                 ,clmns.is_nullable   
                                 ,t.name AS DATA_TYPE  
          
                                 ,CONVERT(tinyint,   
                                     CASE -- int/decimal/numeric/real/float/money    
                                         WHEN clmns.system_type_id IN (48, 52, 56, 59, 60, 62, 106, 108, 122, 127) THEN clmns.precision    
                                     END  
                                  ) AS NUMERIC_PRECISION   
          
                                 ,CONVERT(int,   
                                     CASE -- datetime/smalldatetime    
                                         WHEN clmns.system_type_id IN (40, 41, 42, 43, 58, 61) THEN NULL    
                                         ELSE ODBCSCALE(clmns.system_type_id, clmns.scale)   
                                     END  
                                  ) AS NUMERIC_SCALE  
          
                                 ,CONVERT(smallint,   
                                      CASE -- datetime/smalldatetime    
                                         WHEN clmns.system_type_id IN (40, 41, 42, 43, 58, 61) THEN ODBCSCALE(clmns.system_type_id, clmns.scale)   
                                      END  
                                 ) AS DATETIME_PRECISION   
                         ) AS tComputedProperties    
          
                     WHERE clmns.object_id = userDefinedTableTypes.type_table_object_id   
                     ORDER BY tComputedProperties.ORDINAL_POSITION   
          
                     FOR XML PATH(''), TYPE   
                 ).value('.', 'nvarchar(MAX)') AS column_definition   
         ) AS tAllColumns    
          
     WHERE userDefinedTypes.is_user_defined = 1   
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  4. sourav dutta 201 Reputation points
    2020-10-07T08:18:17.613+00:00

    Thank you very much for your reply

    It works for above said UDT. But it is giving error in below UDT

    CREATE TYPE [dbo].[ProductID] AS TABLE(
    [item_id] [int] NULL
    )

    It returns below

    IF NOT EXISTS (SELECT * FROM sys.types st JOIN sys.schemas ss ON st.schema_id = ss.schema_id WHERE st.name = N'ProductID' AND ss.name = N'dbo') CREATE TYPE [dbo].[ProductID] AS TABLE ( [item_id] int(10, 0) );

    It is giving below error when I run it on other schema

    Msg 2724, Level 16, State 4, Line 1
    Parameter or variable 'item_id' has an invalid data type.