TYPE_NAME (Transact-SQL)

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 and Troubleshooting Metadata Visibility.

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 AdventureWorks2008R2 database.

USE AdventureWorks2008R2;
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)

See Also

Reference