Returns the unqualified type name of a specified type ID.
TYPE_NAME ( 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.
Returns NULL on error or if a caller does not have permission to view the object.
In SQL Server, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as TYPE_NAME may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration.
TYPE_NAME will return NULL when type_id is not valid or when the caller does not have sufficient permission to reference the type.
TYPE_NAME works for system data types and also for user-defined data types. The type can be contained in any schema, but an unqualified type name is always returned. This means the name does not have the schema**.** prefix.
The following example returns the object name, column name, and type name for each column in the Vendor table of the AdventureWorks2012 database.
USE AdventureWorks2012; GO 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; GO
Here is the result set.
obj_name col_name type_name
--------------- ------------------------ --------------
Vendor AccountNumber AccountNumber
Vendor ActiveFlag Flag
Vendor BusinessEntityID int
Vendor CreditRating tinyint
Vendor ModifiedDate datetime
Vendor Name Name
Vendor PreferredVendorStatus Flag
Vendor PurchasingWebServiceURL nvarchar
(8 row(s) affected)