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--

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

%%CODEBLOCK_1%%

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

%%CODEBLOCK_2%%

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., 
  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

%%CODEBLOCK_1%%

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

%%CODEBLOCK_2%%

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.

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 > 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 > 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 <> -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

StackExchange Database Administrators Q#245468, answer score: 6