TYPE_NAME (Transact-SQL)
Returns the unqualified type name of a specified type ID.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
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
Exceptions
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.
Remarks
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.
System functions can be used in the select list, in the WHERE clause, and anywhere an expression is allowed. For more information, see Expressions (Transact-SQL) and WHERE (Transact-SQL).
Examples
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)