Modify database table structure according to SQL script

Recently, a small tool is required for the project.

Requirement: the customer uses the old database and stores some data. After a while, we modified the new database based on the new features proposed by the customer. These modifications have many details, including stored procedures, adding tables, modifying table field types, and adding fields.

After updating and testing the software, we need to change the old database of the customer. The data of the old database is saved.

Solution 1: I soon thought of using the functions provided by SQL SERVER 08 R2 to generate a new library script. Change the old database name, run the new database script, and then import the old database data to the new database through the built-in functions of the database. During the test, the data volume is small and the speed is satisfactory.

However, this is inconvenient for customers, and it is time-consuming to increase the data volume. So let me make a small tool for boss.

Do I need to use a program to implement the above operations? oh my god!

So I want...

Solution 2: I will take a look at the table in the database. I will rename the table in the database and run a new script to create the table, insert the data in the old table.

The related 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 operations:

Reference Database, table, and column rename

List reference content for easy viewing

1. Change the Database Name

Sp_renamedb

Change the database name.

Syntax

Sp_renamedb [@ dbname =] 'old_name ',

[@ Newname =] 'new_name'

Parameters

[@ Dbname =] 'old_name'

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

[@ Newname =] 'new_name'

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

Return code value

0 (successful) or non-zero number (failed)

Permission

Only members of sysadmin and dbcreator fixed server roles can execute sp_renamedb.

Example

In the following example, change the database accounting to financial.

EXEC sp_renamedb 'accounting', 'financial'

Ii. Change the table name or column name

Sp_rename [@ objname =] 'object_name ',

[@ Newname =] 'new_name'

[, [@ Objtype =] 'object_type']

//////////////////////////////////////// /////

A. rename A table

In the following example, the customers table is renamed as custs.

EXEC sp_rename 'customer', 'custs'

B. rename a column

In the following example, the contact title column in the customers table is renamed as the title.

EXEC sp_rename 'customer'. [contact title] ', 'title', 'column'

Refer to the SQL statements used to copy table structures and table data

List reference content for easy viewing

1. Copy the table structure and data to the new table

Create table new table select * FROM old TABLE

2. Only copy the table structure to the new table

Create table new table select * FROM old table where 1 = 2

That is, make the WHERE condition invalid.

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 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 the two tables have different structures)

Insert into new table (Field 1, Field 2 ,......) SELECT Field 1, Field 2 ,...... FROM old table

Think of this, it seems that something is missing, er, procedure and constraint. Well, I will delete it before adding it.

First check out

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 constraints on prefix, refer to SQL constraints prefix.

Easy to view and list

-- Primary key

Constraint PK _ field primary key (field ),

-- Unique constraint

Constraint UK _ field unique key (field ),

-- Default Constraint

Constrint DF _ field default ('default') for field,

-- Check Constraints

Constraint CK _ field check (constraint. For example, len (field)> 1 ),

-- Relationship between primary and Foreign keys

Constraint FK _ master table _ slave table foreign (foreign key field) references master table (primary key field of the master table)

Then read the corresponding script file from the script and execute it (to execute multiple lines of script files, just add '\ n' to CommandText ).

Solution 3: Think about why you have to import data and directly change the table structure. Therefore, fields in the table are read.

Select column_name, data_type from information_schema.columns where table_name = 'tablename'

For fields 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, you can update the structure of the old database without importing data, except for using some ADO. NET, and uses regular expressions to match the corresponding SQL commands in the script. Of course, there are still some problems in the current solution. This tool is not general enough and the redundant columns in the old table are not deleted. It is not considerate to consider fault tolerance. Further improvements will be made in the future based on project requirements.