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:
- 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.
- Drop the UDDT
- Create new UDDT
- 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
]]>