TYPE_NAME (Transact-SQL)

Returns the unqualified type name of a specified type ID.

Topic link iconTransact-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 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 AdventureWorks database.

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)