How to create a SQL User-Defined Table Type identical to an existing one with a new name?

Question

I am trying to create a tool that will find User-Defined Table Types in a database on MS SQL Server that start with ABC and then create a new set of User-Defined Table Types that are identical, but with -X appended to the end of the name.

So, for instance, if I ran this theoretical tool on an existing SQL database that contains three UDT's named MyUdt, YourUdt, and ABCUdt, the result would be that the database would now contain four UDT's: MyUdt, YourUdt, ABCUdt, and ABCUdt-X.

This appears to be rather similar to a question about how to create a UDT that matches a table: Automatically generate a user defined table type that matches an existing table

I know that a SQL database contains the definition of each of its stored procedures as text and this can be queried on the sys tables, but it looks like there is no such definition stores for UDTs.

Before I go to the trouble to write code that will unravel an existing UDT into a statement to create a new UDT, can anyone tell me if there is a way to write a command that says, "Hey MS SQL Server, take the UDT ABCUdt and create another one exactly like it called ABCUdt-X"?

Do you need triggers and what not copied too or just the bare structure, the table and its columns? And with or without data? — sticky bit
I am trying to create a tool An application? Usng what language? SMO is the class library that supports this functionality. — SMor
In SSMS and VS it's able to script the UDT's one at a time. Copy/paste no? — SteveC
@stickybit I am copying a user defined table type, not a table. — Vivian River

AlwaysLearning AlwaysLearning · Accepted Answer · 2021-01-30T00:20:13

As with User-Defined Tables SQL Server stores the definitions of the User-Defined Table Types and their columns in system views, e.g.:

drop type if exists dbo.ABCTest;

create type dbo.ABCTest as table (
  TestID int not null,
  TestName nvarchar(50)
);

declare @type_table_object_id int = (
    select type_table_object_id
    from sys.table_types
    where [name] = N'ABCTest'
);
select * from sys.table_types where type_table_object_id = @type_table_object_id;

select * from sys.all_columns where object_id = @type_table_object_id;

name	system_type_id	user_type_id	schema_id	principal_id	max_length	precision	scale	collation_name	is_nullable	is_user_defined	is_assembly_type	default_object_id	rule_object_id	is_table_type	type_table_object_id	is_memory_optimized
ABCTest	243	257	1	NULL	-1	0	0	NULL	0	1	0	0	0	1	1778105375	0

object_id	name	column_id	system_type_id	user_type_id	max_length	precision	scale	collation_name	is_nullable	is_ansi_padded	is_rowguidcol	is_identity	is_computed	is_filestream	is_replicated	is_non_sql_subscribed	is_merge_published	is_dts_replicated	is_xml_document	xml_collection_id	default_object_id	rule_object_id	is_sparse	is_column_set	generated_always_type	generated_always_type_desc	encryption_type	encryption_type_desc	encryption_algorithm_name	column_encryption_key_id	column_encryption_key_database_name	is_hidden	is_masked	graph_type	graph_type_desc
1778105375	TestID	1	56	56	4	10	0	NULL	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	NOT_APPLICABLE	NULL	NULL	NULL	NULL	NULL	0	0	NULL	NULL
1778105375	TestName	2	231	231	100	0	0	Latin1_General_100_CI_AS	1	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	NOT_APPLICABLE	NULL	NULL	NULL	NULL	NULL	0	0	NULL	NULL

You could regenerate a SQL definition of a User-Defined Table Type from this information to achieve recreating it with a different name, but as SMor said in the comments it's probably easier to make SMO do that work for you.

How to create a SQL User-Defined Table Type identical to an existing one with a new name?

1 Answers