Alter user defined type in SQL Server

Question

I created few user defined types in my database as below

CREATE TYPE [dbo].[StringID] FROM [nvarchar](20) NOT NULL 

and assigned them to various tables. The tables in my database are in various schemas (not only dbo)

But I realized I need bigger field, and I need to alter, e.g increase from [nvarchar](20) to [nvarchar](50), but there is no ALTER TYPE statement.

I need a script that uses a temp table/cursor whatever and saves all the tables and fields where my type is used. Then change existing fields to base type - e.g. from CustID [StringID] to CustID [nvarchar(20)]. Drop the user type and recreate it with new type - e.g. nvarchar(50) and finally set back fields to user type

I do not have rules defined on types, so don't have to drop rules and re-add them.

Any help is appreciated.

Answer 1

This is what I normally use, albeit a bit manual:

/* Add a 'temporary' UDDT with the new definition */  exec sp_addtype t_myudt_tmp, 'numeric(18,5)', NULL    /* Build a command to alter all the existing columns - cut and  ** paste the output, then run it */  select 'alter table dbo.' + TABLE_NAME +         ' alter column ' + COLUMN_NAME + ' t_myudt_tmp'  from INFORMATION_SCHEMA.COLUMNS  where DOMAIN_NAME = 't_myudt'   /* Remove the old UDDT */  exec sp_droptype t_mydut   /* Rename the 'temporary' UDDT to the correct name */  exec sp_rename 't_myudt_tmp', 't_myudt', 'USERDATATYPE'