How to alter a User Defined Data Type?

The only way to alter a User Defined Data Type is to create a new User Define Data Type (UDDT), and change out all existing column to that UDDT, then you can drop the original one, and recreate it and change out the change you made previously.  The problem is that you can't drop the UDDT if it is in use.

In case of that a UDDT is in use, you need to follow the steps below:

1. Allocate a different data type for the fields which are using the UDDT. Rather than allocating an arbitrary data type it is better to allocate a data type of the UDDT, that way there won’t be any issue with the existing data.
2. Drop the UDDT
3. Create new UDDT
4. Allocate the new UDDT to the fields which had previous UDDT.

You can use the following script and you can change the first variable value according to your requirement. This script was tested for several data types.

SET NOCOUNT ON

DECLARE @udt VARCHAR(150)
DECLARE @udtschema VARCHAR(150)
DECLARE @newudtschema VARCHAR(150)
DECLARE @newudtDataType VARCHAR(150)
DECLARE @newudtDataSize smallint
DECLARE @OtherParameter VARCHAR(50)

SET @udt = 'Name' -- Existing UDDT
SET @udtschema = 'dbo' -- Schema of the UDDT
SET @newudtDataType = 'varchar' -- Data type for te new UDDT
SET @newudtDataSize = 500 -- Lenght of the new UDDT
SET @newudtschema = 'dbo' -- Schema of the new UDDT
SET @OtherParameter = ' NULL' -- Other parameters like NULL , NOT NULL
DECLARE @Datatype VARCHAR(50),
    @Datasize SMALLINT

DECLARE @varcharDataType VARCHAR(50)

DECLARE @Schemaname VARCHAR(50),
    @TableName VARCHAR(50),
    @FiledName VARCHAR(50)

CREATE TABLE #udtflds
    (
      Schemaname VARCHAR(50),
      TableName VARCHAR(50),
      FiledName VARCHAR(50)
    )

SELECT TOP 1
        @Datatype = Data_type,
        @Datasize = character_maximum_length
FROM    INFORMATION_SCHEMA.COLUMNS
WHERE   Domain_name = @udt
        AND Domain_schema = @udtschema

SET @varcharDataType = @Datatype
IF @DataType Like '%char%'
    AND @Datasize IS NOT NULL
    AND ( @newudtDataType <> 'varchar(max)'
          OR @newudtDataType <> 'nvarchar(max)'
        )
    BEGIN
        SET @varcharDataType = @varcharDataType + '('
            + CAST(@Datasize AS VARCHAR(50)) + ')'
    END

INSERT  INTO #udtflds
        SELECT  TABLE_SCHEMA,
                TABLE_NAME,
                Column_Name
        FROM    INFORMATION_SCHEMA.COLUMNS
        WHERE   Domain_name = @udt
                AND Domain_schema = @udtschema

DECLARE @exec VARCHAR(500)

DECLARE alter_cursor CURSOR
    FOR SELECT  Schemaname,
                TableName,
                FiledName
        FROM    #udtflds

OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
            + '  ALTER COLUMN ' + @FiledName + ' ' + @varcharDataType
        EXECUTE ( @exec
               )
        FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

    END

CLOSE alter_cursor

SET @exec = 'DROP TYPE [' + @udtschema + '].[' + @udt + ']'
EXEC ( @exec
    )

SET @varcharDataType = @newudtDataType

IF @newudtDataType Like '%char%'
    AND @newudtDataSize IS NOT NULL
    AND ( @newudtDataType <> 'varchar(max)'
          OR @newudtDataType <> 'nvarchar(max)'
        )
    BEGIN
        SET @varcharDataType = @varcharDataType + '('
            + CAST(@newudtDataSize AS VARCHAR(50)) + ')'
    END

SET @exec = 'CREATE TYPE [' + @newudtschema + '].[' + @udt + '] FROM '
    + @varcharDataType + ' ' + @OtherParameter
EXEC ( @exec
    )

OPEN alter_cursor
FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName

WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName
            + '  ALTER COLUMN ' + @FiledName + ' ' + '[' + @newudtschema
            + '].[' + @udt + ']'
        EXECUTE ( @exec
               )
        FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName
    END

CLOSE alter_cursor
DEALLOCATE alter_cursor
SELECT  *
FROM    #udtflds

DROP TABLE #udtflds