SQL modifies the database name, table name, and field name

SQL modifies the database name, table name, and field name

[Materials from the network]

Modify database name: alter database original database name modify name = new database name

 

ADD field: alter table Supplier ADD LoginPassword varchar (20) No need to ADD COLUMN

 

Modify TABLE field attributes: alter table Supplier alter column LoginPassword varchar (30)

 

 

Set the primary key: alter table tableName add constraint PK_id primary key (pkColumnName)

 

Remove primary key: alter table tableName drop constraint pk_id

 

SP_RENAME

 

 

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'

 

If you encounter in my blog, please comment sparingly.

[Materials from the network]

Modify database name: alter database original database name modify name = new database name

 

ADD field: alter table Supplier ADD LoginPassword varchar (20) No need to ADD COLUMN

 

Modify TABLE field attributes: alter table Supplier alter column LoginPassword varchar (30)

 

 

Set the primary key: alter table tableName add constraint PK_id primary key (pkColumnName)

 

Remove primary key: alter table tableName drop constraint pk_id

 

SP_RENAME

 

 

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'