TYPE_NAME
Returns the unqualified type name of a specified type ID.
Syntax
TYPE_NAME ( type_id )
Arguments
type_id
Is the ID of the type that will be used. type_id is an int, and it can refer to a type in any schema that the caller has permission to access.
Return types
sysname
Example
SELECT o.name AS obj_name , c.name AS col_name , TYPE_NAME(c.user_type_id) AS type_name FROM sys.objects AS o JOIN sys.columns AS c ON o.object_id = c.object_id WHERE o.name = 'Vendor' ORDER BY col_name;