List user defined table types in SQL Server (UDT)

How to list user defined table types in a SQL Server database? First of all, users declare User Defined Table Type as a particular table structure in the database. This UDT is reusable in database, it is not a system table. It’s a good practice to ensure users can’t insert data with different types or length for example. Or even from different data structure.

For example, it is very possible that a user insert numbers in a texts filed. So the controls on the input screen and all over the process are key.

T-SQL script to list user defined table types in a SQL Server database (UDT)

For example, we use User Defined Tables (the abbreviation is UDT) objects to call stored procedures. But also functions and they help to control the input parameters conditions.

It means that instead of passing one or more parameters as variables, we pass a UDT to the procedure or function. The goal is to validate the input data structure.

To list User Defined Tables simply execute this T-SQL query to retrieve data from the table_types system table :

SELECT
	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
FROM 	SYS.TABLE_TYPES
WHERE 	IS_USER_DEFINED = 1;

The result is the list of all the user defined table types available in the SQL Server database. Along with their names and other metadata.

To sum-up, this short MS SQL tutorial do not explain how to create a user defined table. But simply how to list them. It is useful to perform checks and impact analysis on a SQL development project.

Check out how to create a table with a T-SQL.