List user defined table types in 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 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.

Be the first to comment

Leave a Reply

Your email address will not be published.


*