SQL implements table name change, column name change, and constraint change

1. Modify Table Name

   Format: sp_rename tablename, newtablename

   sp_rename tablename,newtablename

2. Modify Field name

   Format: sp_rename 'tablename. colname', newcolname, 'column'

   sp_rename 'tablename.colname',newcolname,'column'

3. Add Field

   Format: alter table table_name add new_column data_type [interality_codition]

   Example 1 alter table student Add nationality varchar (20) -- Example 2 add int type columns, default value: 0 alter table student Add studentName int default 0 -- Example 3 add int type columns, the default value is 0, primary key alter table student add studentId int primary key default 0 -- Example 4 check whether the name field exists in student and delete the field if exists (select * from syscolumns where id = object_id ('student ') and name = 'name') begin alter table student drop column name end

4. Change Field

   Format: alter table table_name alter column column_name

   ALTER TABLE student ALTER COLUMN name VARCHAR(200)

5. Delete Field

   Format: alter table table_name drop column column_name

   ALTER TABLE student DROP COLUMN nationality;

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_USAGEWHERE TABLE_NAME = 'student'

7. View the default constraint expression of a field (that is, the default value, 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'

8. View the default constraint name of a field

   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'

9. Delete field constraints

   Format: alter table tablename drop constraint constraintname

   ALTER TABLE student DROP CONSTRAINT PK__student__2F36BC5B772B9A0B

10. Add field constraints

    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 Primary Key CONSTRAINT (primary key) -- Primary Key CONSTRAINT PK_stuNO exists, delete if exists (SELECT * FROM sysobjects WHERE name = 'pk _ stuNo 'and xtype = 'pk ') alter TABLE stuInfoDrop Constraint PK_stuNoGo -- Re-add primary key constraint PK_stuNOALTER TABLE stuInfo ADD Constraint PK_stuNo primary key (stuNo) Go -- Example 3 ADD Unique UQ Constraint (Unique CONSTRAINT) -- Unique Constraint exists, delete if exists (SELECT * FROM sysobjects WHERE name = 'uq _ stuid' and xtype = 'uq ') alter TABLE stuInfoDrop Constraint UQ_stuIDGo -- Re-ADD the UNIQUE UQ_stuIDALTER TABLE stuInfo add constraint UQ_stuID UNIQUE (stuID) -- Example 4 ADD Default DF Constraint (Default Constraint) -- UQ_stuNO, delete if exists (SELECT * FROM sysobjects WHERE name = 'df _ stuaddress' and xtype = 'D ') alter TABLE stuInfo Drop Constraint DF_stuAddressGo -- ADD the default constraint DF_stuAddressALTER TABLE stuInfo ADD Constraint DF_stuAddress DEFAULT ('address unknown ') FOR stuAddress -- Example 5 Check CK constraints (Check Constraint) -- if exists (SELECT * FROM sysobjects WHERE name = 'ck _ stuage' and xtype = 'C') Alter TABLE stuInfo Drop Cons