- Modify Table Name
Format: sp_rename tablename,newtablename
Sp_rename Tablename,newtablename
- Modify field names
Format: sp_rename ' Tablename.colname ', Newcolname, ' column '
Sp_rename ' Tablename.colname ', Newcolname, ' column '
- 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
- Change field
Format: ALTER TABLE table_name ALTER COLUMN COLUMN_NAME
Alter TABLE student ALTER COLUMN name VARCHAR (200)
- Delete a field
Format: ALTER TABLE table_name DROP COLUMN column_name
ALTER TABLE student DROP COLUMN nationality;
- 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 '
- 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 '
- 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 '
- Delete a field constraint
Format: ALTER TABLE tablename DROP constraint ConstraintName
ALTER TABLE student DROP CONSTRAINT pk__student__2f36bc5b772b9a0b
- 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)