Modify the precision of Custom Data Types

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.