Returns the unqualified type name of a specified type ID.
Returns NULL on error or if a caller does not have permission to view the object.
In SQL Server 2005, 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 and Troubleshooting Metadata Visibility.
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
USE AdventureWorks; 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 CreditRating tinyint Vendor ModifiedDate datetime Vendor Name Name Vendor PreferredVendorStatus Flag Vendor PurchasingWebServiceURL nvarchar Vendor VendorID int (8 row(s) affected)