Modify the precision of Custom Data Types

/* -- Example of modifying the precision of Custom Data Types

Once a custom data type is referenced, it cannot be modified or deleted. If you want to modify the data precision, it is very troublesome. The following example shows how to modify

Assume that the custom variable to be modified is named AA.

--*/

-- 1. Modify the name of the custom variable type
Exec sp_rename 'A', 'aa _ Bak', 'userype ype'
Go

-- 2. Add custom variables (based on new precision)
Exec sp_addtype n 'A', n'numeric (20, 2) ', n' not null'
Go

-- 3. Modify the table and use the new custom variable.

Declare @ s varchar (8000)
Declare TB cursor local
For select 'alter table ['+ object_name (A. ID) +'] alter column ['
+ A. Name + ']'
From syscolumns a join policypes B on A. xusertype = B. xusertype
Where B. Name = 'aa _ Bak'
Open TB
Fetch next from TB into @ s
While @ fetch_status = 0
Begin
Exec (@ s)
Fetch next from TB into @ s
End
Close TB
Deallocate TB