List User Defined Table Types with SQL Server

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 User Defined Table types from SQL Server current 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

Be the first to comment

Leave a Reply