Batch modify table layout of the same structure

Source: Internet
Author: User

Recently, the company's business system must be synchronized with the overall business system version, for more than ten years, the company's business system has not synchronized with the latest version of the overall business system, the company handed over the table layout to me for processing, because the company's business system copied many tables, and these tables have the same structure, there are also rules for naming, such as ss01, and ss0101, ss0102 ....

After comparing with the general table layou, I found that the structure of all the table layout fields has not changed, which makes me very fond of reading and admiring it, the person who originally designed the table layou designed how perfect the table was. Ten years later, the accuracy of some fields changed, for example, decimal ), to decimal (), or to modify the value of the default value, I only need to modify the accuracy and the value of the default value.

But each table has dozens of tables with the same structure. How can this problem be solved? I need to modify the table structure one by one, so I need to do it in the Year of the Monkey! (PS: The company's business system has a total of more than 1000 tables.) Later, I thought of a method to steal data: generate the command using the upload MARK: print it out directly, then print the sentence in the print line! How can I calculate layout faster than the two tables of the company and the General Department? I wrote the structure of the table's design procedure into the Excel file, including the field name, type, and length. I only need to add a new parameter value at a glance, you have to go to the design workshop to find the default value. You need to modify the accuracy or the default value as needed!

Sentence:

 

Code

1
2
3 select name from sysobjects where type = 'U' and name like 'ss82% 'order by name
4
5 -----
6 declare @ dbname nvarchar (128)
7 declare @ qsql nvarchar (4000)
8 declare dblist cursor local for -- create the cursor
9 select name from sysobjects where type = 'U' and name like 'ss82% 'order by name
10 For read only -- do not allow update through the upload mark
11 open dblist
12 fetch next from dblist into @ dbname
13 while (fetch_status <>-1)
14 begin
15 set @ qsql =''
16 -- modify the default value
17 set @ qsql = 'alter table' + @ dbname + 'drop constraint DF _ '+ @ dbname +' _ amtd'
18 set @ qsql = @ qsql + 'alter table' + @ dbname + 'add constraint DF _ '+ @ dbname +' _ amtd '+ 'default (0) for amtd'
19 -- modify the table structure
20 -- set @ qsql = @ qsql + 'alter table' + @ dbname + 'alter column amt2 decimal (18,3 )'
21 -- set @ qsql = @ qsql + 'alter table' + @ dbname + 'alter column qty decimal (18,3 )'
22 -- set @ qsql = @ qsql + 'alter table' + @ dbname + 'alter column amt1 decimal (18,3 )'
23 -- set @ qsql = @ qsql + 'alter table' + @ dbname + 'alter column amt0 decimal (18, 3 )'
24 -- set @ qsql = @ qsql + 'alter table' + @ dbname + 'alter column upri decimal (13, 6 )'
25 print @ qsql
26
27 fetch next from dblist into @ dbname
28 end
29 close dblist
30 deallocate dblist
31
32 -- delete default value
33 select * From sysobjects where object_name (parent_obj) = 'ss 83KM'
34 -- the execution result can also obtain the table default value information.
35 --------------------------------------------------------------------
36 -- make sure that the default name is available.
37 alter table [Table name] Drop constraint [default name]
38
39 /************************************** **************************************** *********/
40 -- alter table logss83sf drop constraint df_logss83sf_logdt
41 ---
42
43 select * From sysobjects where object_name (parent_obj) = 'ss821f'
44 alter table logss83sf drop constraint df_logss83sf_logdt
45
46
47

  

 

 

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.