/* -- 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