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. <\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
To list User Defined Tables simply execute this T-SQL query to retrieve data from the table_types system table :<\/p>\n\n\n\n
SELECT\n\tname, \n\tsystem_type_id, \n\tuser_type_id, \n\tschema_id, \n\tprincipal_id, \n\tmax_length, \n\tprecision, \n\tscale, \n\tcollation_name, \n\tis_nullable, \n\tis_user_defined, \n\tis_assembly_type, \n\tdefault_object_id, \n\trule_object_id, \n\tis_table_type\nFROM \tSYS.TABLE_TYPES\nWHERE \tIS_USER_DEFINED = 1;\n<\/pre>\n\n\n\nThe result is the list of all the user defined<\/a> table types available in the SQL Server database. Along with their names and other metadata.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
Check out how to create a table with a T-SQL<\/a><\/strong>.<\/p>\n\n\n\n