Modify database table structure based on SQL script

Recently because of the project need to do a small tool.

Requirements: The customer used the old library and stored some data, using a period of time, we based on the new features of customers in the old library based on the new library to modify. These modifications have many details, including stored procedures, adding tables, modifying table field types, and adding fields.

Then we update and test the software, we need to change the customer side of the old library, the old library data is to be saved.

Solution One: I soon thought of using SQL SERVER R2 features to generate a new library script. Change the old library name, run a new library script, and then through the database with the function of the old library data into the new library data. When testing, the amount of data is small, the speed is also more ideal.

But this is still very inconvenient for customers, and the large amount of data is more time-consuming. So, boss let me make a small tool.

Do I have to use the program to achieve the above operation, oh my God!

So I thought, think ...

Solution Two: I will be more detailed, directly manipulate the database table, for the table in the database, I renamed it, ran a new script to build the table, and then put the data into the cousin.

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 related actions:

Reference database, table, column renaming

List reference content for easy viewing

First, change the database name

Sp_renamedb

Change the name of the database.

Grammar

Sp_renamedb [@dbname =] ' Old_name ',

[@newname =] ' New_name '

Parameters

[@dbname =] ' Old_name '

is the current name of the database. Old_name is a sysname type with no default value.

[@newname =] ' New_name '

is the new name of the database. New_name must follow the rules for identifiers. New_name is a sysname type with no default value.

Return code value

0 (successful) or not 0 digits (failed)

Permissions

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

Example

The following example renames the database accounting to financial.

EXEC sp_renamedb ' accounting ', ' financial '

Change the table name or column name

sp_rename [@objname =] ' object_name ',

[@newname =] ' New_name '

[, [@objtype =] ' object_type ']

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

A. Renaming tables

The following example renames the table customers to custs.

EXEC sp_rename ' customers ', ' custs '

B. Renaming columns

The following example renames the column contact title in table customers to title.

EXEC sp_rename ' customers. [Contact Title] ', ' title ', ' COLUMN '

SQL statements that refer to Replication table structure and table data

List reference content for easy viewing

1. Copy table structure and data to new table

CREATE Table New Table SELECT * from old table

2. Copy table structure to new table only

CREATE Table New Table SELECT * from old table WHERE 1=2

That is, let the Where condition not be tenable.

Method Two: (provided by Tianshibao)

CREATE table new table like old table

3. Copy old table data to new table (assuming two table structure)

INSERT into new table SELECT * from old table

4. Copy old table data to new table (assuming two table structures are different)

INSERT into new Table (Field 1, Field 2,......) SELECT Field 1, Field 2,...... From old table

Think of this, and found that there seems to be something missing, uh, procedure and constraint, well, then I'll remove and add

Check it out first.

Select name from Sys.procedures

Select Constraint_name, table_name from information_schema.table_constraints

and then delete

drop procedure [dbo]. [Procedure_name]

ALTER TABLE tablename drop CONSTRAINT [Pk_ | Fk_ |uk_ | df_ | Ck_]

Here's something to add, about constraint prefixes, reference SQL constraint prefixes

Easy to view and then list out

--PRIMARY key

Constraint pk_ Field primary key (field),

--Unique constraint

Constraint uk_ field unique key (field),

--Default constraint

Constrint df_ field default (' Default value ') for field,

--CHECK constraints

Constraint ck_ field check (constraint. such as: Len (field) >1),

--Primary foreign key relationship

Constraint Fk_ Main Table _ from table foreign (foreign key field) references primary table (primary table primary key field)

Then read the corresponding script file from the script and execute (add a nonsense: To execute Multiline script file, CommandText Riga ' n ' just fine).

Solution Three: Reasoning, why do not import data, directly change the table structure is good. So read the fields in the table

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

This makes it possible to update the old library structure without importing the data, in addition to using some ado.net knowledge, and using regular expressions to match the corresponding SQL commands in the script. Of course, there are still some problems in the current scheme, the tool is not general enough, the need for the old table redundant columns are not deleted, and fault-tolerant considerations are not thoughtful enough. The latter will be further improved according to the project needs.