>Is there a way to change a user defined data type? I have a data type defined as nchar(10) that I want to make nchar(12). SQL Server complains that the data type is in use (which it is). I tried EM but it does not let me change the length either.
>
>TIA
>Trevor
How to change or alter a user defined data type?
Unfortunately, there is no easy way to alter or modify a user defined data type. To modify a user defined data type, follow these steps:
Alter all the tables, that are referencing this user defined data type (UDT), using ALTER TABLE...ALTER COLUMN command and change the data type of the referencing column to an equivalent (or the intended) base data type.
Drop the user defined data type using sp_droptype.
Recreate the user defined datatype with the required changes using sp_addtype.
Again, use the ALTER TABLE...ALTER COLUMN syntax to change the column's datatype to the user defined data type.
----------------------------------------
from
http://vyaskn.tripod.com/administration_faq.htm#q12
If it's not broken, fix it until it is.
My Blog