In SQL Server, you can use the sp_rename stored procedure to rename a user created object in the current database, including a user-defined data type.

Syntax

The syntax goes like this:

EXEC sp_rename 'old_datatype', 'new_datatype', 'USERDATATYPE';

Where old_datatype is the name of the user defined data type you’d like to rename, and new_datatype is the new name you want to give it.

Example

Let’s take a look at the current user defined data types in our database.

SELECT 
  name,
  max_length,
  [precision],
  scale,
  is_user_defined
FROM sys.types
WHERE is_user_defined = 1;

Result:

 +----------+--------------+-------------+---------+-------------------+
 | name     | max_length   | precision   | scale   | is_user_defined   |
 |----------+--------------+-------------+---------+-------------------|
 | clientid | 8            | 0           | 0       | 1                 |
 +----------+--------------+-------------+---------+-------------------+ 

We have one user defined data type called clientid.

Let’s rename it to clientcode.

EXEC sp_rename 'clientid', 'clientcode', 'USERDATATYPE';

Running that renames it to clientcode. You may see the following message:

Caution: Changing any part of an object name could break scripts and stored procedures.

This basically gives us the heads up that if we have any scripts of stored procedures that reference our renamed object, then they will break, and we’ll need to update them.

You may wonder if this means that any existing columns that use this data type will now be broken?

Fortunately, existing columns should be fine.

After renaming the user defined data type, any columns that use that data type will continue to use that data type with its new name. Any restrictions of the data type (such as length, etc) continue to be enforced.

Now let’s check our list of user defined data types again:

SELECT 
  name,
  max_length,
  [precision],
  scale,
  is_user_defined
FROM sys.types
WHERE is_user_defined = 1;

Result:

+------------+--------------+-------------+---------+-------------------+
 | name       | max_length   | precision   | scale   | is_user_defined   |
 |------------+--------------+-------------+---------+-------------------|
 | clientcode | 8            | 0           | 0       | 1                 |
 +------------+--------------+-------------+---------+-------------------+ 

As expected, it has been renamed as specified.