Environment: SQL Server 2008 R2
Problem: Modifying table column names
Solve:
Modify the table's column name in SQL Server to invoke the stored procedure sp_rename.
Use test;--using the database
sp_rename ' D_s_t.avg_grade ', ' avg_g ', ' column ';
--d_s_t is the table name, Avg_grade is the original column name, Avg_g is the new column name----
exec sp_rename ' d_s_t.avg_grade ', ' avg_g ', ' column ';
Attention:
1.avg_g does not have a table name in front of it, otherwise the changed column name is d_s_t. Avg_g.
2. After a successful change, there is a warning: "Note: Changing any part of an object name can corrupt scripts and stored procedures." Don't bother, this is normal.
Attached reference:
sp_rename
Changes the name of a user-created object (such as a table, column, or user-defined data type) in the current database.
Grammar
sp_rename [@objname =] ' object_name ',
[@newname =] ' New_name '
[, [@objtype =] ' object_type ']
Parameters
[@objname =] ' object_name '
is the current name of a User object (table, view, column, stored procedure, trigger, default value, database, object, or rule) or data type. If the object you want to rename is a column in the table, then object_name must be a table.column form. If the index is to be renamed, then the object_name must be a table.index form. object_name is a nvarchar (776) type with no default value.
[@newname =] ' New_name '
is the new name of the specified object. New_name must be part of the name and follow the rules for identifiers. NewName is a sysname type with no default value.
[@objtype =] ' Object_type '
Is the type of object to rename. Object_type is a varchar (13) type with a default value of NULL and the following values are desirable.
Value Description
Column to rename.
Database user-defined databases. This option is required to rename the database.
Index user-defined indexes.
OBJECT is the type of item that is tracked in sysobjects. For example, object can be used to rename constraints (CHECK, FOREIGN key, Primary/unique key), user tables, views, stored procedures, triggers, and rules.
Userdatatype the user-defined data type that is added by executing the sp_addtype.
Return code value
0 (successful) or not 0 digits (failed)