List all SQL Server User Defined Table Types with a query

How to list all UDT or User Defined Tables Types of your SQL Server database? A UDT Type or User Defined Table Type is a particular table structure declared in the database by a user and not by the system. It’s useful to be sure users don’t insert data with different types or length for example, or even with different column names.

For example, UDT is used to call Stored Procedures and functions and to ensure the parameters conditions are fulfilled.

 

List All User Defined Tables Types from SQL Server database

To list all User Defined Table Types, execute this query:

 

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

 

Be the first to comment

Leave a Reply