TYPE_NAME (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns the unqualified type name of a specified type ID.

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.

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)

Examples: Azure SQL Data Warehouse and Parallel Data Warehouse

The following example returns the TYPE ID for the data type with id 1.

SELECT TYPE_NAME(36) AS Type36, TYPE_NAME(239) AS Type239;  
GO  

For a list of types, query sys.types.

SELECT * FROM sys.types;  
GO  

See Also

TYPE_ID (Transact-SQL)
TYPEPROPERTY (Transact-SQL)
sys.types (Transact-SQL)
Metadata Functions (Transact-SQL)