SQL statement used by sqlserver to modify the column name and table name

Source: Internet
Author: User

The Code is as follows:
EXEC sp_rename 'table name. [original column name] ', 'new column name', 'column'

Transact-SQL reference
Sp_rename
Change the name of the user-created object (such as a table, column, or user-defined data type) in the current database.

Syntax

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 to be renamed is a column in the table, the object_name must be in the form of table. column. If you want to rename an index, object_name must be in table. index format. Object_name is nvarchar (776) type and has 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 of the identifier. Newname is of the sysname type and has no default value.
[@ Objtype =] 'object _ type'
Is the type of the object to be renamed. Object_type is of the varchar (13) type, and its default value is NULL. the following values are recommended.
Value description
The COLUMN to be renamed.
DATABASE User-Defined DATABASE. This option is required when you want to rename a database.
INDEX user-defined INDEX.
Project of the OBJECT type tracked in sysobjects. For example, objects can be renamed with constraints (CHECK, foreign key, PRIMARY/unique key), user tables, views, stored procedures, triggers, rules, and other objects.
USERDATATYPE defines the data type added by executing sp_addtype.
Return code value
0 (successful) or non-zero number (failed)
Note
Only the object name or data type name in the current database can be changed. The names of most system data types and System Objects cannot be changed.
When you rename a view, the information about the view in the sysobjects table is updated. When you rename a stored procedure, the information about the procedure in the sysobjects table is updated.
Whenever you rename the primary key or UNIQUE constraint, sp_rename automatically renames the associated index. If the renamed index is associated with the primary key constraint, sp_rename will automatically rename the primary key.
Important after renaming stored procedures and views, clear the process cache to ensure that all related stored procedures and views are recompiled.
Because both stored procedures and views do not store data, both objects can be deleted and rebuilt quickly. To obtain the best results when renaming a Text object, delete the object and recreate it with its new name.
Permission
Sysadmin fixed server role members, db_owner, and db_ddladmin fixed database role members or object owners can execute sp_rename. Only sysadmin and dbcreator fixed server role members can use "database" as object_type to execute sp_rename.
Example
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, rename the contact title column in the customers table to the title column.
EXEC sp_rename 'customer'. [contact title] ', 'title', 'column'

See
ALTER TABLE
CREATE DEFAULT
CREATE PROCEDURE
CREATE RULE
CREATE TABLE
CREATE TRIGGER
CREATE VIEW
Data Type
SETUSER
Sp_addtype
Sp_depends
Sp_renamedb
System stored procedures

Note: The system data table cannot be changed.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.