SQL Server Implementation Change table name, change column name, change constraint code _android

No more nonsense to say, the details are as follows:

1. Modify the table name

Format: sp_rename tablename,newtablename

2. Modify Field Name

Format: sp_rename ' Tablename.colname ', Newcolname, ' column '

3. Add fields

Format: ALTER TABLE table_name add New_column data_type [interality_codition]

Example 1

ALTER TABLE Student ADD Nationality varchar (20)

--Example 2 adds a column of type int with a default value of 0

ALTER TABLE student add Studentname int default 0--Example 3 adds a column of type int with a default value of 0, a primary key 
ALTER TABLE student add StudentID int Prima Ry key Default 0--Example 4 determines if the Name field exists in student and deletes the field 
if exists (SELECT * from syscolumns where id=object_id (' student ') and Name= ' name ') BEGIN 
ALTER TABLE student DROP COLUMN Name 

4. Change fields

Format: ALTER TABLE table_name ALTER COLUMN COLUMN_NAME

5. Delete fields

Format: ALTER TABLE table_name DROP COLUMN column_name

6. View field constraints

Format: SELECT * FROM information_schema.constraint_column_usage WHERE table_name = table_name

SELECT Table_name,column_name,constraint_name from INFORMATION_SCHEMA. Constraint_column_usage

7. View the field default constraint expressions (that is, default values, and so on)

Format: SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = table_name

SELECT table_name, COLUMN_NAME, Column_default from INFORMATION_SCHEMA. COLUMNS

8. View the field DEFAULT constraint name

Format: select name from sysobjects where OBJECT_ID (table_name) =parent_obj and xtype= ' D '

Select name from sysobjects

9. Delete a field constraint

Format: ALTER TABLE tablename DROP constraint ConstraintName

10. Add Field Constraint

Format: ALTER TABLE tablename ADD constraint ConstraintName primary key (column_name)

--Example 1

ALTER TABLE stuinfo Add CONSTRAINT pk_stuno PRIMARY key (Stuno)--Example 2 add a PRIMARY KEY constraint (PRIMARY key)--there is a PRIMARY KEY constraint Pk_stuno, delete if EXI STS (SELECT * from sysobjects WHERE name= ' Pk_stuno ' and xtype= ' PK ') Alter TABLE stuinfo Drop Constraint Pk_stuno Go--Re-add PRIMARY KEY constraint Pk_stuno ALTER TABLE stuinfo add CONSTRAINT pk_stuno PRIMARY key (Stuno) Go--Example 3 adds a unique UQ constraint (unique CONSTRAINT)--exists only A constraint Uq_stuno, delete the IF EXISTS (SELECT * from sysobjects WHERE name= ' uq_stuid ' and xtype= ' UQ ') Alter TABLE stuinfo Drop Constra int Uq_stuid Go--re-add unique constraint uq_stuid ALTER TABLE stuinfo add CONSTRAINT uq_stuid Unique (stuid)--Example 4 add default DF constraint Nstraint)--The default constraint Uq_stuno is present, delete the IF EXISTS (SELECT * from sysobjects WHERE name= ' df_stuaddress ' and xtype= ' D ') Alter TABLE Stuinfo Drop Constraint df_stuaddress Go--re-add default constraint df_stuaddress ALTER TABLE stuinfo ADD Constraint df_stuaddress T (' address unknown ') for stuaddress--Example 5 checks the CK constraint (check Constraint)--there is a check constraint Uq_stuno, delete if EXISTS (SELECT * from sysobjects WHERE n Ame= ' Ck_stuage ' and XtypE= ' C ') Alter TABLE stuinfo Drop Cons 

The above is a small set of SQL Server implementation to introduce changes to the table name, change the column name, change the constraint code, I hope to help you!