Modify the table name and field name in an SQL statement.

Modify the table name and field name in an SQL statement.

 

Today, we have a temporary task to modify the database table name and field name in the production environment. In the past, the field names of the table names to be modified were all in the development environment and opened directly.

It is okay for SQL Server to rename the corresponding table or field, but this is an online database, and it is impossible to directly modify F2. What should I do? Hey

Write scripts. I learned some relevant information online and summarized some practical problems during my operations to further deepen the printing process.

Image.

 

SQL comes with a series of system stored procedures. Sp_rename can modify the table name and column name. This is true for sp_rename.

Modify the name of the user-created object (such as a table, column, or user-defined data type) in the current database.

 

Basic syntax

 

Modify Table Name: EXEC sp_rename '[original table name]', '[new table name]';

Modify the COLUMN name: EXEC sp_rename '[original COLUMN name]', '[new COLUMN name]', 'column ';

 

Baidu encyclopedia Link

Http://baike.baidu.com/link? Url = 62aL-_ e-KkdpZjw-dx_uAnOu8xEs9nuLd0TPn-ESZZy_hw2ahMYtjUVZyWToQn6w9r7lB_H_5qLmF4aj645Njq

 

Instance description

There are three tables in the existing database testDB, framework dbo, and dbo: animal (code, name), bird (code, name, animalCode '), cat (code, name, animalcode ')

Next, I will perform the following operations on these three tables:

Modify Table Name, column name, attribute type, and attribute

<Span style = "font-family: Verdana; font-size: 18px;"> USE testDBGO -- change the name of the animal table to animalCategoryEXEC sp_rename '[dbo]. [animal] ', 'animalcategory'; -- change the foreign key animalCode in the bird table to animalCategoryCodeEXEC sp_rename '[dbo]. [bird]. animalCode ', 'animalcategorycode', 'column'; -- change the type of the attribute name in the bird TABLE to varcharalter table [dbo]. [bird] alter column name VARCHAR (20) -- add the color foreign key alter table [dbo] to the bird TABLE. [bird] ADD colorCode int nullgo </span>

 

Note that an exception is thrown after the following script is executed:

Message 15248, level 11, status 1, process sp_rename, 238th rows
The @ objname parameter is not clear or the @ objtype (COLUMN) declared is incorrect.

 

<Span style = "font-family: Verdana; font-size: 18px;"> USE testDBGO -- change the cat table name to petCatEXEC sp_rename 'cat', 'petcat '; -- change the foreign key animalCode in the bird table to animalCategoryCodeEXEC sp_rename 'cat. animalCode ', 'animalcategorycode', 'column'; GO </span>

In this case, there is nothing wrong with modifying the table name and column name. Then, you will find that the cat table has been killed after the first SQL statement is executed.

The name is petCat, so the table cat cannot be found when the second SQL statement is executed again.

 

Therefore, if you want to change the table name and field name at the same time, pay attention to the order of modification or remember: First Change the column and then change the table

 

Note that

My script does not give the frame body, that is, the table's qualified condition dbo. Dbo is used by default when no table conditions are specified.

Error. If the cat table is qualified as tmp. cat, this script throws an exception because the cat table cannot be found under the default dbo.

 

Therefore, when operating a table, you must develop a good habit: always have a frame body.

 

 

Feelings

There are not many knowledge points, but it is also the harvest of today.

Compared with the first point, this is more important: clear thinking logic and good programming habits!

 

 

In the executed SQL statement, another stored procedure sys. sp_addextendedproperty is applied for a simple example. If you are interested

You can check it online.

 

<Span style = "font-family: Verdana; font-size: 18px; ">=================================================== =======================================-- Author: wyq -- skill: whether to add the student information table to the instructor's Children attributes-Date: -- Description: -- whether to add the student information table to the instructor's Children attributes ========================================== ============================ TRAN in tran use BasicServiceALTER TABLE dbo. studentInfo ADDIsToTeacher bit nullexec sys. sp_addextendedproperty @ name = n' MS _ description', @ value = n' whether it is a teacher's child ', @ level0type = n' SCHEMA', @ level0name = n' dbo ', @ level1type = n' TABLE ', @ level1name = n' studentinfo', @ level2type = n' COLUMN', @ level2name = n' istoteacher' -- commit tran </span>

Keywords: Database modification table name modification column name modification attribute type sp_rename

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.