how to script out the user defined table types? I can Get name and definition of all table types
using either of the following scripts:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED


IF OBJECT_ID('TEMPDB..#RADHE') IS NOT NULL
   DROP TABLE #RADHE

CREATE TABLE #RADHE
(
 RADHE SYSNAME,
 COLUMN_NAME SYSNAME,
 TYPE_COLUMN SYSNAME,
 PRIMARY KEY CLUSTERED (RADHE,COLUMN_NAME)
 )

DECLARE @sql nvarchar(max) = N'', 
  @stub nvarchar(max) = N'SELECT [RADHE]=N''$--RADHE--$'', 
   COLUMN_NAME=name, TYPE_COLUMN=system_type_name
   FROM sys.dm_exec_describe_first_result_set(''DECLARE 
   @tvp $--RADHE--$; SELECT * FROM @tvp;'',null,null)
   ORDER BY column_ordinal;';

SELECT @sql += REPLACE(@stub, N'$--RADHE--$', 
  QUOTENAME(s.name) + N'.' + QUOTENAME(t.name))
FROM sys.table_types AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id];

INSERT INTO #RADHE 
EXEC sys.sp_executesql @sql;

SELECT * FROM #RADHE




SELECT
    tt.name AS table_type_name,
    c.name AS column_name,
    c.column_id,
    t.name AS type_name,
    c.max_length,
    c.precision,
    c.scale,
    c.collation_name,
    c.is_nullable
FROM 
    sys.columns As c
    JOIN sys.table_types AS tt
        ON c.object_id = tt.type_table_object_id
    JOIN sys.types AS t
        ON t.user_type_id = c.user_type_id
ORDER BY
    tt.name,
    c.column_id




and I can even GRANT REFERENCE on all user defined types using the following script:

    SELECT t.name, 
           'GRANT REFERENCES ON TYPE::' 
           + SCHEMA_NAME(t.schema_id) 
           + '.' 
           + t.name 
           + ' TO public;' AS command_to_run
    FROM   sys.types AS t
where 1=1
 AND T.is_table_type = 1




But is there a way to script out all table types in a database?

I am looking to script out this table type, please note the constraints and index created with it:

use TableBackups
go

IF EXISTS(SELECT * 
            FROM SYS.table_types tt
           WHERE tt.NAME=N'DistCritGroupData' 
             AND SCHEMA_NAME(tt.SCHEMA_ID) = N'dbo')
   DROP TYPE DBO.DistCritGroupData

CREATE TYPE [dbo].[DistCritGroupData] AS TABLE
  (
    [DistCritTypeId] [int] NOT NULL UNIQUE,
    [ItemAction] [int] NOT NULL,        
    [ObjectId] [int] NOT NULL,
    [OperatorType] [int] NOT NULL,

    PRIMARY KEY NONCLUSTERED 
    (
       [DistCritTypeId] ASC
    ),

    INDEX CIX CLUSTERED (ObjectId, OperatorType)    
  );
Honestly, the amount of time you'll spend writing a version of this that accounts for all possible combinations of indexes, constraints, and default values, and troubleshoot all the combinations you don't expect from your first use case, I don't think you'll ever get that time back no matter how many table types you have to script, which you can always do from Object Explorer (or Object Explorer Details, for multiple):


  


Just for fun, I ran a trace to see what Management Studio sends to SQL Server in order to generate the create script for that table type, and it was about as pretty as I expected:

exec sp_executesql N'SELECT
SCHEMA_NAME(tt.schema_id) AS [Schema],
tt.name AS [Name]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)',
N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',
@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'


exec sp_executesql N'SELECT
clmns.column_id AS [ID],
clmns.name AS [Name],
clmns.is_ansi_padded AS [AnsiPaddingStatus],
ISNULL(clmns.collation_name, N'''') AS [Collation],
clmns.column_encryption_key_id AS [ColumnEncryptionKeyID],
ceks.name AS [ColumnEncryptionKeyName],
clmns.is_computed AS [Computed],
ISNULL(cc.definition,N'''') AS [ComputedText],
s1clmns.name AS [DataTypeSchema],
(case when clmns.default_object_id = 0 then N'''' when d.parent_object_id &gt; 0 then N'''' else d.name end) AS [Default],
ISNULL(dc.Name, N'''') AS [DefaultConstraintName],
(case when clmns.default_object_id = 0 then N'''' when d.parent_object_id &gt; 0 then N'''' else schema_name(d.schema_id) end) AS [DefaultSchema],
clmns.encryption_algorithm_name AS [EncryptionAlgorithm],
CAST(clmns.encryption_type AS int) AS [EncryptionType],
clmns.generated_always_type AS [GeneratedAlwaysType],
ISNULL(clmns.graph_type, 0) AS [GraphType],
clmns.is_identity AS [Identity],
CAST(ISNULL(ic.seed_value,0) AS numeric(38)) AS [IdentitySeedAsDecimal],
CAST(ISNULL(ic.increment_value,0) AS numeric(38)) AS [IdentityIncrementAsDecimal],
CAST(0 AS bit) AS [IsClassified],
CAST(clmns.is_column_set AS bit) AS [IsColumnSet],
CAST(clmns.is_filestream AS bit) AS [IsFileStream],
CAST(ISNULL((select TOP 1 1 from sys.foreign_key_columns AS colfk where colfk.parent_column_id = clmns.column_id and colfk.parent_object_id = clmns.object_id), 0) AS bit) AS [IsForeignKey],
CAST(clmns.is_masked AS bit) AS [IsMasked],
CAST(ISNULL(cc.is_persisted, 0) AS bit) AS [IsPersisted],
CAST(clmns.is_sparse AS bit) AS [IsSparse],
CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND clmns.max_length &lt;&gt; -1 THEN clmns.max_length/2 ELSE clmns.max_length END AS int) AS [Length],
ISNULL((SELECT ms.masking_function FROM sys.masked_columns ms WHERE ms.object_id = clmns.object_id AND ms.column_id = clmns.column_id), N'''') AS [MaskingFunction],
ISNULL(ic.is_not_for_replication, 0) AS [NotForReplication],
clmns.is_nullable AS [Nullable],
CAST(clmns.scale AS int) AS [NumericScale],
CAST(clmns.precision AS int) AS [NumericPrecision],
CAST(clmns.is_rowguidcol AS bit) AS [RowGuidCol],
(case when clmns.rule_object_id = 0 then N'''' else r.name end) AS [Rule],
(case when clmns.rule_object_id = 0 then N'''' else schema_name(r.schema_id) end) AS [RuleSchema],
ISNULL(baset.name, N'''') AS [SystemType],
ISNULL(xscclmns.name, N'''') AS [XmlSchemaNamespace],
ISNULL(s2clmns.name, N'''') AS [XmlSchemaNamespaceSchema],
ISNULL( (case clmns.is_xml_document when 1 then 2 else 1 end), 0) AS [XmlDocumentConstraint],
usrt.name AS [DataType]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
LEFT OUTER JOIN sys.column_encryption_keys AS ceks ON (ceks.column_encryption_key_id = clmns.column_encryption_key_id)
LEFT OUTER JOIN sys.computed_columns AS cc ON cc.object_id = clmns.object_id and cc.column_id = clmns.column_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = clmns.user_type_id
LEFT OUTER JOIN sys.schemas AS s1clmns ON s1clmns.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.objects AS d ON d.object_id = clmns.default_object_id
LEFT OUTER JOIN sys.default_constraints as dc ON clmns.default_object_id = dc.object_id
LEFT OUTER JOIN sys.identity_columns AS ic ON ic.object_id = clmns.object_id and ic.column_id = clmns.column_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = clmns.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = clmns.system_type_id) and (baset.user_type_id = clmns.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) 
LEFT OUTER JOIN sys.objects AS r ON r.object_id = clmns.rule_object_id
LEFT OUTER JOIN sys.xml_schema_collections AS xscclmns ON xscclmns.xml_collection_id = clmns.xml_collection_id
LEFT OUTER JOIN sys.schemas AS s2clmns ON s2clmns.schema_id = xscclmns.schema_id
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'


exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=clmns.column_id AND p.class=8
WHERE
(clmns.name=@_msparam_0)and((tt.name=@_msparam_1 and SCHEMA_NAME(tt.schema_id)=@_msparam_2))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'DistCritTypeId',@_msparam_1=N'DistCritGroupData',@_msparam_2=N'dbo'



exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=clmns.column_id AND p.class=8
WHERE
(clmns.name=@_msparam_0)and((tt.name=@_msparam_1 and SCHEMA_NAME(tt.schema_id)=@_msparam_2))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'ItemAction',@_msparam_1=N'DistCritGroupData',@_msparam_2=N'dbo'



exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=clmns.column_id AND p.class=8
WHERE
(clmns.name=@_msparam_0)and((tt.name=@_msparam_1 and SCHEMA_NAME(tt.schema_id)=@_msparam_2))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'ObjectId',@_msparam_1=N'DistCritGroupData',@_msparam_2=N'dbo'


exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tt.type_table_object_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=clmns.column_id AND p.class=8
WHERE
(clmns.name=@_msparam_0)and((tt.name=@_msparam_1 and SCHEMA_NAME(tt.schema_id)=@_msparam_2))
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000)',@_msparam_0=N'OperatorType',@_msparam_1=N'DistCritGroupData',@_msparam_2=N'dbo'

exec sp_executesql N'SELECT
p.name AS [Name],
CAST(p.value AS sql_variant) AS [Value]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.extended_properties AS p ON p.major_id=tt.user_type_id AND p.minor_id=0 AND p.class=6
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
i.name AS [Name],
CAST(ISNULL(si.bounding_box_xmax,0) AS float(53)) AS [BoundingBoxXMax],
CAST(ISNULL(si.bounding_box_xmin,0) AS float(53)) AS [BoundingBoxXMin],
CAST(ISNULL(si.bounding_box_ymax,0) AS float(53)) AS [BoundingBoxYMax],
CAST(ISNULL(si.bounding_box_ymin,0) AS float(53)) AS [BoundingBoxYMin],
CAST(case when (i.type=7) then hi.bucket_count else 0 end AS int) AS [BucketCount],
CAST(ISNULL(si.cells_per_object,0) AS int) AS [CellsPerObject],
CAST(i.compression_delay AS int) AS [CompressionDelay],
~i.allow_page_locks AS [DisallowPageLocks],
~i.allow_row_locks AS [DisallowRowLocks],

        CASE WHEN ((SELECT tbli.is_memory_optimized FROM sys.tables tbli WHERE tbli.object_id = i.object_id)=1 or
        (SELECT tti.is_memory_optimized FROM sys.table_types tti WHERE tti.type_table_object_id = i.object_id)=1)
        THEN ISNULL((SELECT ds.name FROM sys.data_spaces AS ds WHERE ds.type=''FX''), N'''')
        ELSE CASE WHEN ''FG''=dsi.type THEN dsi.name ELSE N'''' END
        END
       AS [FileGroup],
CASE WHEN ''FD''=dstbl.type THEN dstbl.name ELSE N'''' END AS [FileStreamFileGroup],
CASE WHEN ''PS''=dstbl.type THEN dstbl.name ELSE N'''' END AS [FileStreamPartitionScheme],
i.fill_factor AS [FillFactor],
ISNULL(i.filter_definition, N'''') AS [FilterDefinition],
i.ignore_dup_key AS [IgnoreDuplicateKeys],

        ISNULL(indexedpaths.name, N'''')
       AS [IndexedXmlPathName],
i.is_primary_key + 2*i.is_unique_constraint AS [IndexKeyType],
CAST(
          CASE i.type WHEN 1 THEN 0 WHEN 4 THEN 4
                      WHEN 3 THEN CASE xi.xml_index_type WHEN 0 THEN 2 WHEN 1 THEN 3 WHEN 2 THEN 7 WHEN 3 THEN 8 END
                      WHEN 4 THEN 4 WHEN 6 THEN 5 WHEN 7 THEN 6 WHEN 5 THEN 9 ELSE 1 END
        AS tinyint) AS [IndexType],
CAST(CASE i.index_id WHEN 1 THEN 1 ELSE 0 END AS bit) AS [IsClustered],
i.is_disabled AS [IsDisabled],
CAST(CASE WHEN filetableobj.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS [IsFileTableDefined],
CAST(ISNULL(k.is_system_named, 0) AS bit) AS [IsSystemNamed],
CAST(OBJECTPROPERTY(i.object_id,N''IsMSShipped'') AS bit) AS [IsSystemObject],
i.is_unique AS [IsUnique],
CAST(ISNULL(si.level_1_grid,0) AS smallint) AS [Level1Grid],
CAST(ISNULL(si.level_2_grid,0) AS smallint) AS [Level2Grid],
CAST(ISNULL(si.level_3_grid,0) AS smallint) AS [Level3Grid],
CAST(ISNULL(si.level_4_grid,0) AS smallint) AS [Level4Grid],
ISNULL(s.no_recompute,0) AS [NoAutomaticRecomputation],
CAST(ISNULL(INDEXPROPERTY(i.object_id, i.name, N''IsPadIndex''), 0) AS bit) AS [PadIndex],
ISNULL(xi2.name, N'''') AS [ParentXmlIndex],
CASE WHEN ''PS''=dsi.type THEN dsi.name ELSE N'''' END AS [PartitionScheme],
case UPPER(ISNULL(xi.secondary_type,'''')) when ''P'' then 1 when ''V'' then 2 when ''R'' then 3 else 0 end AS [SecondaryXmlIndexType],
CAST(ISNULL(spi.spatial_index_type,0) AS tinyint) AS [SpatialIndexType]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id &gt; @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
LEFT OUTER JOIN sys.spatial_index_tessellations as si ON i.object_id = si.object_id and i.index_id = si.index_id
LEFT OUTER JOIN sys.hash_indexes AS hi ON i.object_id = hi.object_id AND i.index_id = hi.index_id
LEFT OUTER JOIN sys.data_spaces AS dsi ON dsi.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.tables AS t ON t.object_id = i.object_id
LEFT OUTER JOIN sys.data_spaces AS dstbl ON dstbl.data_space_id = t.Filestream_data_space_id and (i.index_id &lt; 2 or (i.type = 7 and i.index_id &lt; 3))
LEFT OUTER JOIN sys.xml_indexes AS xi ON xi.object_id = i.object_id AND xi.index_id = i.index_id
LEFT OUTER JOIN sys.selective_xml_index_paths AS indexedpaths ON xi.object_id = indexedpaths.object_id AND xi.using_xml_index_id = indexedpaths.index_id AND xi.path_id = indexedpaths.path_id
LEFT OUTER JOIN sys.filetable_system_defined_objects AS filetableobj ON i.object_id = filetableobj.object_id
LEFT OUTER JOIN sys.key_constraints AS k ON k.parent_object_id = i.object_id AND k.unique_index_id = i.index_id
LEFT OUTER JOIN sys.stats AS s ON s.stats_id = i.index_id AND s.object_id = i.object_id
LEFT OUTER JOIN sys.xml_indexes AS xi2 ON xi2.object_id = xi.object_id AND xi2.index_id = xi.using_xml_index_id
LEFT OUTER JOIN sys.spatial_indexes AS spi ON i.object_id = spi.object_id and i.index_id = spi.index_id
WHERE
(tt.name=@_msparam_2 and SCHEMA_NAME(tt.schema_id)=@_msparam_3)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'DistCritGroupData',@_msparam_3=N'dbo'


exec sp_executesql N'SELECT
cstr.name AS [Name],
cstr.is_not_for_replication AS [NotForReplication],
~cstr.is_not_trusted AS [IsChecked],
~cstr.is_disabled AS [IsEnabled],
CAST(cstr.is_system_named AS bit) AS [IsSystemNamed],
CAST(CASE WHEN filetableobj.object_id IS NULL THEN 0 ELSE 1 END AS bit) AS [IsFileTableDefined],
cstr.definition AS [Text]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.check_constraints AS cstr ON cstr.parent_object_id=tt.type_table_object_id
LEFT OUTER JOIN sys.filetable_system_defined_objects AS filetableobj ON filetableobj.object_id = cstr.object_id
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)
ORDER BY
[Name] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
ISNULL(s1tt.name, N'''') AS [Owner],
CAST(case when tt.principal_id is null then 1 else 0 end AS bit) AS [IsSchemaOwned],
tt.name AS [Name],
tt.type_table_object_id AS [ID],
SCHEMA_NAME(tt.schema_id) AS [Schema],
obj.create_date AS [CreateDate],
obj.modify_date AS [DateLastModified],
tt.max_length AS [MaxLength],
tt.is_nullable AS [Nullable],
ISNULL(tt.collation_name, N'''') AS [Collation],
CAST(case when tt.is_user_defined = 1 then 1 else 0 end AS bit) AS [IsUserDefined],
CAST(tt.is_memory_optimized AS bit) AS [IsMemoryOptimized]
FROM
sys.table_types AS tt
LEFT OUTER JOIN sys.database_principals AS s1tt ON s1tt.principal_id = ISNULL(tt.principal_id, (TYPEPROPERTY(QUOTENAME(SCHEMA_NAME(tt.schema_id)) + ''.'' + QUOTENAME(tt.name), ''OwnerId'')))
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
LEFT OUTER JOIN sys.objects AS obj ON obj.object_id = tt.type_table_object_id
WHERE
(tt.name=@_msparam_0 and SCHEMA_NAME(tt.schema_id)=@_msparam_1)',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000)',@_msparam_0=N'DistCritGroupData',@_msparam_1=N'dbo'

exec sp_executesql N'SELECT
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id &gt; @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id &gt; 0 and (ic.key_ordinal &gt; 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(i.name=@_msparam_2)and((tt.name=@_msparam_3 and SCHEMA_NAME(tt.schema_id)=@_msparam_4))
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'CIX',@_msparam_3=N'DistCritGroupData',@_msparam_4=N'dbo'

exec sp_executesql N'SELECT
clmns.name AS [Name],
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id &gt; @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id &gt; 0 and (ic.key_ordinal &gt; 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(clmns.name=@_msparam_2)and((i.name=@_msparam_3)and((tt.name=@_msparam_4 and SCHEMA_NAME(tt.schema_id)=@_msparam_5)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'ObjectId',@_msparam_3=N'CIX',@_msparam_4=N'DistCritGroupData',@_msparam_5=N'dbo'

exec sp_executesql N'SELECT
clmns.name AS [Name],
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id &gt; @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id &gt; 0 and (ic.key_ordinal &gt; 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(clmns.name=@_msparam_2)and((i.name=@_msparam_3)and((tt.name=@_msparam_4 and SCHEMA_NAME(tt.schema_id)=@_msparam_5)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'OperatorType',@_msparam_3=N'CIX',@_msparam_4=N'DistCritGroupData',@_msparam_5=N'dbo'

exec sp_executesql N'SELECT
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id &gt; @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id &gt; 0 and (ic.key_ordinal &gt; 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(i.name=@_msparam_2)and((tt.name=@_msparam_3 and SCHEMA_NAME(tt.schema_id)=@_msparam_4))
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'PK__TT_DistC__199F41EAA68706DB',@_msparam_3=N'DistCritGroupData',@_msparam_4=N'dbo'

exec sp_executesql N'SELECT
clmns.name AS [Name],
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id &gt; @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id &gt; 0 and (ic.key_ordinal &gt; 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(clmns.name=@_msparam_2)and((i.name=@_msparam_3)and((tt.name=@_msparam_4 and SCHEMA_NAME(tt.schema_id)=@_msparam_5)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'DistCritTypeId',@_msparam_3=N'PK__TT_DistC__199F41EAA68706DB',@_msparam_4=N'DistCritGroupData',@_msparam_5=N'dbo'

exec sp_executesql N'SELECT
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
clmns.name AS [Name]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id &gt; @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id &gt; 0 and (ic.key_ordinal &gt; 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(i.name=@_msparam_2)and((tt.name=@_msparam_3 and SCHEMA_NAME(tt.schema_id)=@_msparam_4))
ORDER BY
[ID] ASC',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'UQ__TT_DistC__199F41EAC5355FAA',@_msparam_3=N'DistCritGroupData',@_msparam_4=N'dbo'

exec sp_executesql N'SELECT
clmns.name AS [Name],
(case ic.key_ordinal when 0 then ic.index_column_id else ic.key_ordinal end) AS [ID],
ic.is_descending_key AS [Descending],
ic.is_included_column AS [IsIncluded],
CAST(COLUMNPROPERTY(ic.object_id, clmns.name, N''IsComputed'') AS bit) AS [IsComputed]
FROM
sys.table_types AS tt
INNER JOIN sys.schemas AS stt ON stt.schema_id = tt.schema_id
INNER JOIN sys.indexes AS i ON (i.index_id &gt; @_msparam_0 and i.is_hypothetical = @_msparam_1) AND (i.object_id=tt.type_table_object_id)
INNER JOIN sys.index_columns AS ic ON (ic.column_id &gt; 0 and (ic.key_ordinal &gt; 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id
WHERE
(clmns.name=@_msparam_2)and((i.name=@_msparam_3)and((tt.name=@_msparam_4 and SCHEMA_NAME(tt.schema_id)=@_msparam_5)))',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000),@_msparam_4 nvarchar(4000),@_msparam_5 nvarchar(4000)',@_msparam_0=N'0',@_msparam_1=N'0',@_msparam_2=N'DistCritTypeId',@_msparam_3=N'UQ__TT_DistC__199F41EAC5355FAA',@_msparam_4=N'DistCritGroupData',@_msparam_5=N'dbo'


I mean, that is just awful. And all it does is return all of those resultsets to SSMS; then there is some C# and/or SMO plumbing that actually steps through all the possible combinations and generates the script. This is hidden away in application code that is much harder to trace (especially without violating EULA), and so your ability to determine all of the issues they encountered when generating the script is pretty limited.

So I think if your goal is to have a nice, handy little piece of T-SQL code that is faster than right-clicking a table name in Object Explorer, you're going to be out of luck.

If I didn't have SSMS (or the plan was to create 60,000 table types), I would rather keep track of these creations using a DDL trigger than try to reverse engineer the metadata to generate the original statement. I wrote this tip a long time ago, but the same concepts apply today (you could maybe just filter the types of objects you keep track of):


SQL Server DDL Triggers to Track All Database Changes


Borrowing from that, you could create this table:

CREATE TABLE dbo.TableTypeCreationEvents
(
  EventDate    datetime NOT NULL DEFAULT sysutcdatetime(),
  EventDDL     nvarchar(max),
  SchemaName   nvarchar(128),
  ObjectName   nvarchar(128)
);


And then this trigger:

CREATE TRIGGER DDLCaptureTableTypeCreations ON DATABASE
  FOR CREATE_TYPE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @EventData xml = EVENTDATA();

  DECLARE 
    @sch sysname = @EventData.value(N'(/EVENT_INSTANCE/SchemaName)[1]', N'nvarchar(128)'),
    @obj sysname = @EventData.value(N'(/EVENT_INSTANCE/ObjectName)[1]', N'nvarchar(128)'),
    @s nvarchar(max) 
                 = @EventData.value(N'(/EVENT_INSTANCE/TSQLCommand)[1]', N'nvarchar(max)');

  IF EXISTS (SELECT 1 FROM sys.table_types 
    WHERE name = @obj AND [schema_id] = SCHEMA_ID(@sch))
  BEGIN
    INSERT dbo.TableTypeCreationEvents(EventDDL,SchemaName,ObjectName)
      SELECT @s,@sch,@tab;
  END
END
GO


Now, some time has passed, and you want the script for the most recent version of the type dbo.DistCritGroupData? No problem:

SELECT TOP (1) EventDDL 
  FROM dbo.TableTypeCreationEvents
  WHERE [schema_id] = SCHEMA_ID(N'dbo')
    AND name = N'DistCritGroupData'
  ORDER BY EventDate DESC;


This will return exactly the same script that you ran when you created the type, and since there is no ALTER TYPE, you don't have to worry about catching a table type up to any changes that have been made since its creation.