SQL Server ALTER statement

When modifying the SQL Server table structure, use the ALTER statement to enumerate some commonly used alter statements as follows.

1: Add a field to the table

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

2: Delete Field

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

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

ALTER TABLE [table name] ALTER COLUMN [column name] Type

4: Add primary key

Alter table [table name] add constraint [constraint name] primary KEY ([column name])

5: Add UNIQUE Constraint

Alter table [table name] add constraint [constraint name] Unique ([column name])

6: Add default value for a column in a table

Alter table [table name] add constraint [constraint name] Default (defaults) for [column name]
7: Add constraint

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 Constraint

Alter table [table name] drop constraint [constraint name]

10: Renaming 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 ' for fixed notation)

12: Add descriptive information to the table
EXECUTE sp_addextendedproperty n ' ms_description ', ' Personnel information table ', n ' user ', n ' dbo ', n ' table ', n ' tables name ', NULL, NULL

13: Add descriptive information for field username
EXECUTE sp_addextendedproperty n ' ms_description ', ' name ', n ' user ', n ' dbo ', n ' table ', n ' tables name ', n ' column ', n ' Username '

14: Add descriptive information for field sex
EXECUTE sp_addextendedproperty n ' ms_description ', ' Gender ', n ' user ', n ' dbo ', n ' table ', n ' tables name ', n ' column ', n ' sex '

15: Update the Description property of the column username in the table:
EXEC sp_updateextendedproperty ' ms_description ', ' New name ', ' user ', dbo, ' table ', ' table name ', ' column ', ' UserName '

16: Delete The Description property of the column username in the table:
EXEC sp_dropextendedproperty ' ms_description ', ' user ', dbo, ' table ', ' name ', ' column ', ' Username '

SQL Server ALTER statement