SQL implementation table name change, column name change, constraint change

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

2. Modify Table Name

   Format: sp_rename tablename,newtablename

   sp_rename tablename, Newtablename

4. Modify field names

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

   ' Tablename.colname ', Newcolname,' column '  

5. Add Field

   Format: ALTER TABLE table_name add New_column data_type [interality_codition]

   Example 1ALTER TABLE StudentADD Nationalityvarchar(20)--Example 2 adds a column of type int, the default value is 0ALTER TABLE studentAdd Studentnameint default 0--Example 3 adds a column of type int, the default value is 0, the primary key ALTER TABLE student add studentid int primary key default 0 --Example 4 determine if the Name field exists in student and delete the field if exis TS (Select * from syscolumns where id=object_id(' student ') and name  =' name ') begin ALTER TABLE student DROP COLUMN name end      
                                    

7. Change field

   Format: ALTER TABLE table_name ALTER COLUMN COLUMN_NAME

   VARCHAR($) 

9. Delete a field

   Format: ALTER TABLE table_name DROP COLUMN column_name

   DROP COLUMN nationality;

11. view field Constraints

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

    select table_name,column_name ,constraint_name from .where table_name =  ' student '           

     

13. 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_default information_schema. columnswhere table_name= ' student '           

     

15. View field DEFAULT constraint name

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

    sysobjectsobject_id(' table, name? ') and xtype=' D '     

17. Delete a field constraint

    Format: ALTER TABLE tablename DROP constraint ConstraintName

    DROP CONSTRAINT pk__student__2f36bc5b772b9a0b

18. Add a Field constraint

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

    --Example 1ALTER TABLE StuinfoADD CONSTRAINT Pk_stunoPRIMARY KEY(Stuno)--Example 2 add PRIMARY KEY constraint (Primary key)--Pk_stuno If there is a PRIMARY key constraint, deleteIFEXISTS (SELECT*FromsysobjectsWHERE Name=' Pk_stuno 'and Xtype=' PK ')Alter TABLE StuinfoDrop Constraint Pk_stunoGo--Re-add the primary KEY constraint Pk_stunoALTER TABLE StuinfoADD CONSTRAINT Pk_stunoPRIMARY KEY(Stuno)Go--Example 3 to add a unique UQ constraint (unique Constraint)--a unique constraint Uq_stuno exists, the deletionIFEXISTS (SELECT*FromsysobjectsWHERE Name=' Uq_stuid 'and Xtype=' UQ ')Alter TABLE StuinfoDrop Constraint Uq_stuidGo--Re-add UNIQUE constraint Uq_stuidALTER TABLE StuinfoADD CONSTRAINT Uq_stuidUNIQUE(Stuid)--Example 4 adds a default DF constraint (the default Constraint)--there is a Uq_stuno constraint, deleteIFEXISTS (SELECT*FromsysobjectsWHERE Name=' Df_stuaddress 'and Xtype=D)Alter TABLE StuinfoDrop Constraint df_stuaddressGo--Re-add default constraint df_stuaddressALTER TABLE Stuinfoadd CONSTRAINT df_stuaddress DEFAULT (' 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