SQL Server alter statement

When modifying the SQL Server table structure, we often use the alter statement to list some commonly used alter statements as follows.

1: add fields to the table

Alter table [Table name] add [column name] Type

2: delete a field

Alter table [Table name] Drop column [column name]

3: Modify the field type in the table (you can modify the column type, whether it is null)

Alter table [Table name] alter column [column name] Type

4: Add a primary key

Alter table [Table name] add constraint [constraint name] primary key ([column name])

5. Add a unique constraint

Alter table [Table name] add constraint [constraint name] unique ([column name])

6: add the default value of a column in the table

Alter table [Table name] add constraint [constraint name] default (default) for [column name]
7. Add Constraints

Alter table [Table name] add constraint [constraint name] Check (content)

8: Add a foreign key constraint

Alter table [Table name] add constraint [constraint name] foreign key (column name) referencese another table name (column name)

9: delete Constraints

Alter table [Table name] Drop constraint [constraint name]

10: rename a table

Exec sp_rename '[original table name]', '[new table name]'

11. Rename the column name.

Exec sp_rename '[Table name]. [column name]', '[Table name]. [new column name]'

 

Create comments (N 'user', N 'dbo', N 'table' is a fixed Statement)

12: Add description information for the table
Execute sp_addextendedproperty n 'Ms _ description', 'personnel info table ', N 'user', N 'dbo', N 'table', N 'table name', null, null

13: Add description information for the field Username
Execute sp_addextendedproperty n 'Ms _ description', 'name', N 'user', N 'dbo', N 'table', N 'table name', N 'column ', N 'username'

14: Add description information for the sex field
Execute sp_addextendedproperty n 'Ms _ description', 'gender', N 'user', N 'dbo', N 'table', N 'table name', N 'column ', N 'sex'

15: update the description attribute of the username column in the table:
Exec sp_updateextendedproperty 'Ms _ description', 'new name', 'user', DBO, 'table', 'table name', 'column', 'username'

16: Delete the description attribute of the username column in the table:
Exec sp_dropextendedproperty 'Ms _ description', 'user', DBO, 'table', 'table name', 'column ', 'username'