How to list User Defined Tables Types of your SQL Server database? First of all, users declare User Defined Table Type as a particular table structure in the database. A database user declares it and not the system. It’s good to ensure users can’t insert data with different types or length for example, or even with different data structure.
For example, we use UDT objects to call Stored Procedures and functions and also ensure the parameters conditions are fulfilled.
How to list all user defined table types in a database?
To List User Defined Tables, simply 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
The result is the list of the user defined table types and their names. Check out how to create a table in T-SQL.