SQL Server table structure modification script

1: Add fields to the table

Alter table [Table Name] Add [Column name]Type

2:Delete Field

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

3:Modify Field Types in a 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 value ) for [ column name ] 7 : add constraints

Alter table [Table Name] Add constraint [Constraint name] Check (Content)

8:Add foreign key constraints

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 information table ', n' user', n' dbo', n' table', n' table name ', null, null

13 : username Add description information execute sp_addextendedproperty n 'Ms _ description', ' name ', n' user', n' dbo', n' table', n' table name ', n' column', n' username'

14 : sex Add description information execute sp_addextendedproperty n 'Ms _ description', ' gender ', n' user', n' dbo', n' table', n' table name ', n' column', n' sex '

15 : update table columns username description attribute: exec sp_updateextendedproperty 'Ms _ description', ' New name ', 'user', DBO, 'table',' table name ', 'column', 'username'

16: Delete columns in a tableUsernameDescription attribute:Exec sp_dropextendedproperty 'Ms _ description', 'user', DBO, 'table ','Table Name', 'Column', 'username'

 

original link