Several solutions of modifying database table structure according to SQL script

Time:2021-11-30

Recently, due to the needs of the project, I have to make a small tool.

Requirements: the customer used the old library and stored some data. After a period of time, we modified the new library based on the old library according to the new functions proposed by the customer. These modifications have many details, including stored procedures, adding tables, modifying table field types, and adding fields.

Then, after we update and test the software ourselves, we need to change the old database on the customer’s side, and the data of the old database should be saved.

Solution 1: I soon thought of using the functions of SQL Server 08 R2 to generate new library scripts. Change the name of the old database, run the new database script, and then import the old database data into the new database data through the built-in function of the database. During the test, the amount of data is small and the speed is ideal.

However, this is still very inconvenient for customers, and the large amount of data is still time-consuming. So, boss let me be a gadget.

Should I use a program to achieve the above operations, oh my God!

So I thought and thought….

Solution 2: I’ll be more detailed and directly operate the database tables. For the existing tables in the database, I rename them, run a new script to create the tables, and then insert the data in the old tables.

Relevant SQL statements are as follows:

Modify table name: exec sp_ rename ‘oldName’ , ‘newName’

Insert data: insert into newtable (column1, column2,…) select column1, column2,… From oldtable

More related actions:

Renaming of reference database, table and column

List references for easy viewing

1、 Change database name

Copy codeThe code is as follows:
sp_renamedb
Change the name of the database.

grammar
sp_renamedb [ @dbname = ] ‘ old_name ‘ ,
[ @newname = ] ‘ new_name ‘

parameter
[ @dbname = ] ‘ old_name ‘

Is the current name of the database. old_ Name is of sysname type and has no default value.

[ @newname = ] ‘ new_name ‘

Is the new name of the database. new_ Name must follow identifier rules. new_ Name is of sysname type and has no default value.

Return code value
0 (success) or non-zero number (failure)

jurisdiction
Only members of the sysadmin and dbcreator fixed server roles can execute sp_ renamedb。

Example
The following example renames the database accounting to financial.

EXEC sp_renamedb ‘ accounting ‘ , ‘ financial ‘

2、 Change table or column name

Copy codeThe code is as follows:
sp_rename [ @objname = ] ‘ object_name ‘ ,
[ @newname = ] ‘ new_name ‘
[ , [ @objtype = ] ‘ object_type ‘ ]

/////////////////////////////////////////////
A. Rename table
The following example renames the table customers to customs.

EXEC sp_rename ‘ customers ‘ , ‘ custs ‘

B. Heavy life list
The following example renames the column contact title in the table customers to title.

EXEC sp_rename ‘ customers.[contact title] ‘ , ‘ title ‘ , ‘ COLUMN ‘

Refer to SQL statements that replicate table structures and table data

List references for easy viewing

1. Copy table structure and data to a new table
Create table new table select * from old table

2. Copy only the table structure to the new table
Create table new table select * from old table where 1 = 2

That is, let the where condition not hold

Method 2: (provided by tianshibao)
Create table new table like old table

3. Copy the data from the old table to the new table (assuming that the two tables have the same structure)
Insert into new table select * from old table

4. Copy the data from the old table to the new table (assuming that the two tables have different structures)
Insert into new table (field 1, field 2,….) select field 1, field 2,… From old table

Thinking of this, I found that there seems to be something missing, er, procedure and constraint. Well, I’ll delete it first and then add it
Find out first
select name from sys.procedures
select constraint_name, table_name from information_schema.table_constraints
Delete again
drop procedure [dbo].[procedure_name]
alter table tableName drop CONSTRAINT [PK_ |FK_ |UK_ |DF_ |CK_]
Here are some additional things. For constraint prefixes, refer to SQL constraint prefixes
Easy to view, and then list
–Primary key
constraint PK_ Field primary key,

–Unique constraint
constraint UK_ Field unique key,

–Default constraint
constrint DF_ Field default (‘default ‘) for field,

–Check constraints
constraint CK_ Field check (constraint. For example: len > 1),

–Primary foreign key relationship
constraint FK_ Main table_ From the table foreign (foreign key field) references to the main table (primary key field of the main table)

Then read the corresponding script file from the script and execute it (add a nonsense: to execute a multi line script file, just add ‘\ n’ in commandtext).

Solution 3: think about it. Why do you have to import data? Just change the table structure directly. The fields in the table are then read

select column_name,data_type from information_schema.columns where table_name = ‘tableName’
For fields that exist in the old table
ALTER TABLE [tableName] ALTER COLUMN [columnName] [int] NOT NULL
For fields that do not exist in the old table
ALTER TABLE [tableName] ADD COLUMN [columnName] [int] NOT NULL

In this way, the old library structure can be updated without importing data. In addition to using some ado.net knowledge, regular expressions are also used to match the corresponding SQL commands in the script. Of course, there are still some problems in the current scheme. The tool is not universal enough, the redundant columns in the old table are not deleted, and the consideration of fault tolerance is not considerate enough. It will be further improved according to the needs of the project in the later stage.