In SQL Server, you can use the TYPE_NAME()
function to return the name of a data type, based on its ID. This can be useful when querying a system view such as sys.columns
that returns the type’s ID but not its name.
You can use TYPE_NAME()
for system data types and user-defined data types.
Example 1 – Basic Usage
Here’s a basic example to demonstrate how it works.
SELECT TYPE_NAME(34) AS Result;
Result:
+----------+ | Result | |----------| | image | +----------+
This result tells us that the type ID of 34 is used for the image type.
Example 2 – A More Useful Example
Here’s a more useful example.
USE Music; SELECT o.name AS [Object Name], c.name AS [Column 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.type_desc = 'USER_TABLE';
Result:
+---------------+---------------+-------------+ | Object Name | Column Name | Type Name | |---------------+---------------+-------------| | Artists | ArtistId | int | | Artists | ArtistName | nvarchar | | Artists | ActiveFrom | date | | Artists | CountryId | int | | Genres | GenreId | int | | Genres | Genre | nvarchar | | Albums | AlbumId | int | | Albums | AlbumName | nvarchar | | Albums | ReleaseDate | date | | Albums | ArtistId | int | | Albums | GenreId | int | | Country | CountryId | int | | Country | CountryName | nvarchar | +---------------+---------------+-------------+
This query returns user tables, along with their columns and the data type for each column.
Here’s what it looks like if I remove TYPE_NAME()
:
USE Music; SELECT o.name AS [Object Name], c.name AS [Column Name], c.user_type_id FROM sys.objects AS o JOIN sys.columns AS c ON o.object_id = c.object_id WHERE o.type_desc = 'USER_TABLE';
Result:
+---------------+---------------+----------------+ | Object Name | Column Name | user_type_id | |---------------+---------------+----------------| | Artists | ArtistId | 56 | | Artists | ArtistName | 231 | | Artists | ActiveFrom | 40 | | Artists | CountryId | 56 | | Genres | GenreId | 56 | | Genres | Genre | 231 | | Albums | AlbumId | 56 | | Albums | AlbumName | 231 | | Albums | ReleaseDate | 40 | | Albums | ArtistId | 56 | | Albums | GenreId | 56 | | Country | CountryId | 56 | | Country | CountryName | 231 | +---------------+---------------+----------------+
It’s not as easy to read the type ID.
Example 3 – User-Defined Types
User-defined types are included. Here’s an example that includes a user-defined type alias in the results.
USE Test; SELECT o.name AS [Object Name], c.name AS [Column Name], TYPE_NAME(c.user_type_id) AS [Type Name], CASE WHEN t.is_user_defined = 1 THEN 'Yes' ELSE 'No' END AS [User Defined?] FROM sys.objects AS o JOIN sys.columns AS c ON o.object_id = c.object_id JOIN sys.types t ON c.user_type_id = t.user_type_id WHERE o.type_desc = 'USER_TABLE' AND o.name = 'Client';
Result:
+---------------+---------------+-------------+-----------------+ | Object Name | Column Name | Type Name | User Defined? | |---------------+---------------+-------------+-----------------| | Client | ClientCode | clientcode | Yes | | Client | FirstName | varchar | No | | Client | LastName | varchar | No | +---------------+---------------+-------------+-----------------+
Example 4 – Using TYPE_NAME() in a WHERE Clause
You can use TYPE_NAME()
(and any other system function) in a WHERE
clause (and anywhere an expression is allowed).
Here, I modify the previous example so that I use TYPE_NAME()
in the WHERE
clause.
USE Test; SELECT o.name AS [Object Name], c.name AS [Column Name], TYPE_NAME(c.user_type_id) AS [Type Name], CASE WHEN t.is_user_defined = 1 THEN 'Yes' ELSE 'No' END AS [User Defined?] FROM sys.objects AS o JOIN sys.columns AS c ON o.object_id = c.object_id JOIN sys.types t ON c.user_type_id = t.user_type_id WHERE TYPE_NAME(c.user_type_id) = 'clientcode';
Result:
+---------------+---------------+-------------+-----------------+ | Object Name | Column Name | Type Name | User Defined? | |---------------+---------------+-------------+-----------------| | Client | ClientCode | clientcode | Yes | +---------------+---------------+-------------+-----------------+
Example 5 – Invalid Type ID or Insufficient Permission
If you provide an invalid type ID or you don’t have sufficient permission to reference the type, the result will be NULL.
SELECT TYPE_NAME(258) AS Result;
Result:
+----------+ | Result | |----------| | NULL | +----------+
Get the Type ID
If you already know the data type’s name, but you want its ID, you can use TYPE_ID()
to return the ID of a data type based on its name.