SQL implementation table name change, column name change, constraint change (EXEC)

2. Modify Table Name

   Format: sp_rename tablename,newtablename

   Sp_rename Tablename,newtablename

4. Modify field names

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

   Sp_rename ' Tablename.colname ', Newcolname, ' column '

6. Add Field

   Format: ALTER TABLE table_name add New_column data_type [interality_codition]

   Example 1ALTER table Student Add nationality varchar (20)--Example 2 adds a column of type int, the default value is 0alter TABLE student add studentname int default 0< c0/>--Example 3 adds a column of type int, the default value is 0, the primary key is ALTER TABLE student add StudentID int primary key default 0       --Example 4 determines if the Name field exists in student and deletes field if exists (SELECT * from syscolumns where id=object_id (' student ') and name= ' name ') BEGIN   ALTER TABLE student DROP COL Umn Name    

8. Change field

   Format: ALTER TABLE table_name ALTER COLUMN COLUMN_NAME

   Alter TABLE student ALTER COLUMN name VARCHAR (200)

10. Delete a field

    Format: ALTER TABLE table_name DROP COLUMN column_name

    ALTER TABLE student DROP COLUMN nationality;

12. 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_usagewhere table_name = ' student '

14. View field Default constraint expressions (that is, default values, etc.)

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

    SELECT table_name, COLUMN_NAME, Column_default from INFORMATION_SCHEMA. Columnswhere table_name= ' student '

16. View field DEFAULT constraint name

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

    Select name from Sysobjectswhere object_id (' table name? ') =parent_obj and xtype= ' D '

18. Delete a field constraint

    Format: ALTER TABLE tablename DROP constraint ConstraintName

    ALTER TABLE student DROP CONSTRAINT pk__student__2f36bc5b772b9a0b

19. Add a Field constraint

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

    --Example 1ALTER TABLE stuinfo add CONSTRAINT pk_stuno PRIMARY key (Stuno)--Example 2 add PRIMARY KEY constraint (PRIMARY key)--if there is a PRIMARY KEY constraint Pk_stuno, delete if Exis TS (SELECT * from sysobjects WHERE name= ' Pk_stuno ' and xtype= ' PK ') Alter TABLE stuinfodrop Constraint pk_stunogo--re-add Master Key constraints Pk_stunoalter TABLE stuinfo add CONSTRAINT pk_stuno PRIMARY KEY (Stuno) go--Example 3 Add a unique UQ constraint (unique CONSTRAINT)-there is a unique constraint UQ _stuno, delete if EXISTS (SELECT * from sysobjects WHERE name= ' uq_stuid ' and xtype= ' UQ ') Alter TABLE stuinfodrop Constraint U q_stuidgo--add unique constraint uq_stuidalter TABLE stuinfo add CONSTRAINT uq_stuid Unique (stuid)--Example 4 add default DF constraint Constraint)--there is a default constraint Uq_stuno, delete if EXISTS (SELECT * from sysobjects WHERE name= ' df_stuaddress ' and xtype= ' D ') Alter tabl  E stuinfo Drop Constraint df_stuaddressgo--re-add default constraint df_stuaddressalter TABLE stuinfo add Constraint df_stuaddress defaults (' Address unknown ') For stuaddress--example 5 check CK constraint (check Constraint)--there is a check constraint Uq_stuno, delete if EXISTS (SELECT * from sysobjects WHERE name= ' Ck_ Stuage ' and xtype= ' C ') Alter TABLE Stuinfo Drop Cons 

SQL Implementation table name change, column name change, constraint change (exec)